VBA Exercise （ VBAでの腕試し！）

###Summary

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

これができたら中級者？


###Exercise01

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

```vb
300
800
100
```

結果

```vb
300
1100
1200
```

###Exercise02

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


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

結果

```vb
 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 |


結果

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

###Exercise04

得意先コード（TOKU_CD）`0001`の消費税の合計を求めてください
消費税区分`SZEI_KBN2`は`1 = 0.05, 2 = 0.08, 3 = 0.10`とします
売上区分`URI_KBN`は`1, 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

結果

```vb
1288
```

### Hints and Answers

解答の一例です
下記のヒントと解答にこだわる必要はないです
コードには`Ariawase`と`koffeeVBA`を使ってます

[Ariawase](https://github.com/vbaidiot/Ariawase)
[koffeeVBA](https://github.com/callmekohei/koffeeVBA)


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


### Hint3, Hint4

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

```vb
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 でつけます

```sql
FROM  [test$] AS T
```

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

```sql
 [T.SZEI_KBN2] 
```

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

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

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

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

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

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



###Answer1

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


###Answer2
```vb
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

```vb
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

```vb

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
```