F# Monkey

about

VBA Exercise

Summary

VBAの問題をつくってみました!

これができたら中級者?

Exercise01

下記データーの累計をもとめてください

300
800
100

結果

300
1100
1200

Exercise02

リストの最後尾を取得してください

A B C D E F
1 A1 B1 C1 D1 F1
2 A2
3 A3
4
5 A5

結果

 5  'LastRow
 3  'LastRow(xlDown)
 4  'LastCol
 6  'LastCol(xlRight)

Exercise03

転記の欄にがついてるデーターを取得してください

転記 会社名 郵便番号 住所1 住所2 住所3
油須原郵便局 824-0499 福岡県 田川郡赤村 赤4657-5
甘木郵便局 838-8799 福岡県 朝倉市 甘木948
杷木郵便局 838-1599 福岡県 朝倉市 杷木池田542-1

結果

Array("甘木郵便局")
Array("838-8799")
Array("福岡県")
Array("朝倉市")
Array("甘木948")

Exercise04

得意先コード(TOKU_CD)0001の消費税の合計を求めてください
消費税区分SZEI_KBN21 = 0.05, 2 = 0.08, 3 = 0.10とします
売上区分URI_KBN1, Cは黒伝、それ以外は赤伝とします

TOKU_CD DEN_NO GYO_NO URI_KBN SYHN_CD HINMEI SUU_RYO TANKA SZEI_KBN2
0001 20150115 01 1 001 apple 10 300 2
0001 20150115 01 1 002 banana 16 250 2
0001 20150116 01 1 001 apple 20 300 2
0001 20150116 02 1 002 banana 1 250 2
0001 20150116 03 1 003 tomato 19 150 2
0002 20150117 01 B 001 apple 3 300 1
0002 20150117 02 B 002 banana 5 250 1
0003 20150118 01 1 001 apple 7 300 3
0003 20150118 02 1 002 banana 24 250 3
0003 20150118 03 1 003 tomato 11 150 3

結果

1288



Hints and Answers

解答の一例です

下記のヒントと解答にこだわる必要はないです

コードにはAriawasekoffeeVBAを使ってます

Ariawase

koffeeVBA

Hint2

RangeオブジェクトのEndメソッドを使います

Hint3, Hint4

データーをテーブルと見立ててSQLで処理します
まずスタイルですが、下記のように記入します

Private Function SQL() As String
    Dim str As String:  str = ""
    str = str & " SELECT  xxx "
    str = str & "   FROM  xxx "
    str = str & "  WHERE  xxx "
    str = str & "  GROUP  BY xxx "
    str = str & "  ORDER  BY xxx "
    SQL = str
End Function

テーブルに関しての別名は AS でつけます

FROM  [test$] AS T

項目(タイトル行)やテーブル名は角カッコ【】をつけます

 [T.SZEI_KBN2] 

分岐に関しては SWITCH もしくは IIF をつかいます

SWITCH (
            condition = a, a
          , condition = b, b
          , condition = c, c
          , true, null
       )
       
IIF( condition , a (when true) , b (when false))

テーブルの指定は [worksheet name $] という書き方になります

FROM  [test$]  '<---- ワークシート名が test

テーブルの範囲指定は [worksheet name $A1:F500] という書き方になります

FROM  [test$A1:F500]  '<---- ワークシート名が test 範囲が A1:F500

Answer1

Sub Sample()
    Dim arr As Variant: arr = Array(300, 800, 100)
    Debug.Print Dump(StepTotal(arr))
End Sub

Answer2

Sub Sample()
    Dim sh As Worksheet: Set sh = ThisWorkbook.Worksheets("test")
    Debug.Print LastRow(sh.Range("A1"))
    Debug.Print LastRow(sh.Range("A1"), True)
    Debug.Print LastCol(sh.Range("A1"))
    Debug.Print LastCol(sh.Range("A1"), True)
End Sub

Public Function LastRow(ByVal R As Range, Optional toDonw As Boolean = False) As Long
    Select Case toDonw
        Case True:  LastRow = R.End(xlDown).row
        Case False: LastRow = R.Worksheet.Cells(R.Worksheet.Rows.Count, R.Column).End(xlUp).row
    End Select
End Function

Public Function LastCol(ByVal R As Range, Optional toRight As Boolean = False) As Long
    Select Case toRight
        Case True:  LastCol = R.End(xlToRight).Column
        Case False: LastCol = R.Worksheet.Cells(R.row, R.Worksheet.Columns.Count).End(xlToLeft).Column
    End Select
End Function

Answer3

Sub Sample()

    Dim JagArr As Variant: JagArr = FetchSh(sql())

    Debug.Print Dump(JagArr(1))
    Debug.Print Dump(JagArr(2))
    Debug.Print Dump(JagArr(3))
    Debug.Print Dump(JagArr(4))
    Debug.Print Dump(JagArr(5))

End Sub

Private Function sql() As String
    Dim str As String
    str = ""
    str = str & " SELECT  * "
    str = str & "   FROM  [test$] "
    str = str & "  WHERE  [転記] = '●'"
    sql = str
End Function

Answer4

Sub Sample()
    Debug.Print Dump(FetchSh(SyohiZei_SQL("0001"))(0))
End Sub

Private Function SyohiZei_SQL(ByVal TokuCode As String) As String
    Dim str As String:  str = ""

     str = str & " SELECT "
     str = str & "   SUM( "
     str = str & "     IIF( [T.URI_KBN] IN ('1', 'C') "
     str = str & "          ,SWITCH( "
     str = str & "             [T.SZEI_KBN2] = '1' , [T.TANKA] * [T.SUU_RYO] * 0.05 "
     str = str & "            ,[T.SZEI_KBN2] = '2' , [T.TANKA] * [T.SUU_RYO] * 0.08 "
     str = str & "            ,[T.SZEI_KBN2] = '3' , [T.TANKA] * [T.SUU_RYO] * 0.10 "
     str = str & "            ,TRUE , NULL "
     str = str & "          ) "
     str = str & "          ,SWITCH( "
     str = str & "             [T.SZEI_KBN2] = '1' , [T.TANKA] * [T.SUU_RYO] * -0.05 "
     str = str & "            ,[T.SZEI_KBN2] = '2' , [T.TANKA] * [T.SUU_RYO] * -0.08 "
     str = str & "            ,[T.SZEI_KBN2] = '3' , [T.TANKA] * [T.SUU_RYO] * -0.10 "
     str = str & "            ,TRUE , NULL "
     str = str & "          ) "
     str = str & "        ) "
     str = str & "      ) "

     str = str & " FROM  [test$] AS T "
     str = str & "WHERE  [TOKU_CD] = '" & TokuCode & "'"

     SyohiZei_SQL = str
End Function

Public Function FetchSh(ByVal sql As String, Optional ByVal fPath As String = "", Optional ByVal isHeader As Boolean = False) As Variant

    Select Case fPath
        Case Is = "": fPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name
        Case Else:    fPath = fPath
    End Select

    Dim ado As New AdoEx:   ado.Init adExcel, fPath
    Select Case isHeader
        Case False: FetchSh = ado.JagArrAdoRS(sql)
        Case True:  FetchSh = Array(ado.JagArrAdoRS(sql), ado.JagArrAdoRsHeader(sql))
    End Select

    Set ado = Nothing
End Function






Remove all ads