VBA Tips

####普段仕事か勉強で使えそうな内容をメモしておきます。
######**ワークシートの最大行、最大列を取得する。**（Excleのバージョンにより、戻り値が違う）
 EXCEL97/2000/2003では65,536行、256列。
 EXCEL2007/2010では1,048,576行、16,384列で、
セル数にして17,179,869,184（171億）、従来の1024倍

>[ワークシートの最終行、最終列を取得する](http://www.niji.or.jp/home/toru/notes/8.html)

```vbnet
Dim MaxRow As Long
Dim MaxCol As Long
MaxRow = Rows.Count
MaxCol = Columns.Count
```

######**アクティブシートの使用領域の最終列、最終行の取得**
>[ワークシートの最終行、最終列を取得する](http://www.niji.or.jp/home/toru/notes/8.html)

```vbnet
With ActiveSheet.UsedRange
  MaxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
  MaxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
End With
```

######**特定の終了ボタンだけで終了でき、通常のExcelの操作で終了できないようにする**

```vbnet:Thisworkbookに書く
Private Sub Workbook_BeforeClose(Cancel as Boolean)
  Rem IsCloseButton変数は標準モジュールの宣言セクションでPublicとして宣言する
  If IsColseButton = False Then
    Cancel = True
  ElseIf Workbooks.Count = 1 Then
    Application.Quit
  End
End Sub
```
```vbnet:標準モジュールに書く
Dim IsCloseButton As Boolean
Sub Excel終了()
  Dim MsgResult As VbMsgBoxResult
  MsgResult = MsgBox("Excelを終了しますか？",vbYesNoCancel, "Excelの終了")
  If MsgResult = VbCancel Then
    Exit Sub
  End If
  IsCloseButton = True
  If MsgResult = vbYes Then
    ThisWorkbook.Close savechanges:=True
  Else
   ThisWorkbook.Colse savechanges:=False
  End If
End Sub
```
「Excel終了」プロシージャをボタン専用の終了ボタンのマクロに登録する。

######**タイトルセルがダブルクリックされたら、その項目をタイトルにソートする**
```vbnet
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ 
                                              Cancel As Boolean)
  '----------------------------------------------------------------
  'Application.Intersect(Taget, Range("範囲名称")) Is Nothingで
  'クリックした範囲がタイトル範囲であるかどうかを判断する
  '----------------------------------------------------------------
  If Application.Intersect(Target, Range("タイトルセル範囲")) Is Nothing Then
    'タイトルセル範囲以外のセルがクリックされたので、ソートしない
    Else
    'ダブルクリックされたタイトルセルを使い、五十音順の昇順で並べ替えをする
    Target.Sort key1:=Target, order1:=xlAscending, Header:=xlYes
    Cancel = True
  End If
End Sub
```
######**For Each文を使い、全てのワークシートに対して順番に何らかの処理をする**
```vbnet
Dim Mysheet As Worksheet
For Each Mysheet In Worksheets
  Mysheet.Select
  With ActiveWindow
    .DisplayGridlines = False 'シートの枠線の非表示
    .DisplayHeadings  = False 'シートの行列番号の非表示
  End With
  '或いはページの印刷関連の設定とか…
Next Mysheet
```
######**VbMsgBoxResult型**
VbMsgBoxResult型の変数でMsgBox関数の戻り値を代入するようにすると、コード入力時に自動メンバー表示が有効になり、コードを効率的に記述できます。

```vbnet
Dim MsgResult As VbMsgBoxResult
MsgResult = MsgBox("Excelを終了しますか？",vbYesNoCancel, "Excelの終了")
If MsgResult = vbYes Then
  ...省略
End If
```

######**ウィンドウオプションの表示・非表示**
枠線や行列番号の設定は現在選択されているシートだけが対象となります。

```vbnet
ActiveWindow.DisplayGridlines = True     'シートの枠線の表示
ActiveWindow.DisplayGridlines = False    'シートの枠線の非表示
ActiveWindow.DisplayHeadings = True      'シートの行列番号の表示
ActiveWindow.DisplayHeadings = False     'シートの行列番号の非表示
ActiveWindow.DisplayworkbookTabs = True  'シート見出しの表示
ActiveWindow.DisplayworkbookTabs = False 'シート見出しの非表示
```

######**リボンの表示・非表示**

```vbnet
Application.ExecuteExcel4Macro "Show.Toobar(""Ribbon"",False)" 'リボンを非表示
Application.ExecuteExcel4Macro "Show.Toobar(""Ribbon"",True)"  'リボンを表示
```

######**数式バー、ステータスバーの表示・非表示**

```vbnet
Application.DisplayFormulaBar = True  '数式バーの表示
Application.DisplayFormulaBar = False '数式バーの非表示
Application.DisplayStatusBar  = True  'ステータスバーの表示
Application.DisplayStatusBar  = False 'ステータスバーの非表示
```

######**タイトルバーに表示する文字列の設定・取得**

```vbnet
Application.Caption = "Excel Applicationのタイトル"
ActiveWindow.Caption = "アクティブウィンドウのタイトル"
```

######**画面更新の停止・開始**
```vbnet
Application.ScreenUpdating = False '画面更新の停止
Application.ScrennUpdating = True  '画面更新の開始
```
######**画面アラートの停止・開始**
```vbnet
Application.DisplayAlerts = False '画面アラートの停止
Application.DisplayAlerts = True  '画面アラートの開始
```
######**イベントの停止・開始**
```vbnet
Application.EnableEvents = False   'イベントの停止
Application.EnableEvents = True    'イベントの開始
```
######**シートの保護・シートの保護の解除**

```vbnet
ActiveSheet.Unprotect MyPassword 'MyPassword変数に保存した値でシートの保護を解除
ActiveSheet.Protect   MyPassword 'MyPassword変数に保存した値でシートの保護をする
```

######**WrokSheetオブジェクト.Shapes(リストボックスや図形の名前)**

```vbnet
ActiveSheet.Shapes("リストボックス名前").Visible = False 'リストボックスを非表示
ActiveSheet.Shapes("リストボックス名前").Visible = True  'リストボックスを表示
ActiveSheet.Shapes("図形名前").Visible = False           '図形を非表示
ActiveSheet.Shapes("図形名前").Visible = True            '図形を表示
```
######**リスト表示対象セルがクリックされたら、リストボックスを表示する**
```vbnet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("リスト表示対象セル名前").Address Then
        ActiveSheet.Shapes("リストボックス名前").Visible = True
    Else
        ActiveSheet.Shapes("リストボックス名前").Visible = False
    End If
End Sub
```
######**入力不可のテキストボックスの設定**
```vbnet
'BackColor : &H8000000F&
'Locked    : True
'TabStop   : False
```
######**日付テキストボックスの設定(IMEModeをOFFに)**
```vbnet
'IMEMode : 2-fmIMEModeOff
```
######**セル範囲を使い、コンボボックスを作成**
```vbnet
'RowSource : 名称リスト（名称リストはシート上のセル範囲を名前で定義したもの）
'Style     : 2-fmStyleDropDownList　一覧からの選択だけでき、値の入力はできない
'          : 0-fmStyleDropDownCombo 一覧からの選択と値の入力ができる
```
######**セル範囲を使い、リストボックスを作成**
```vbnet
'ColumnCount : 2
'ColumnHeads : True (RowSourceプロパティに指定したセル範囲の1行上のセルを見出し）
'ColumnWidths: 50pt;40pt
'RowSource   : 名称リスト（名称リストはシート上のセル範囲を名前で定義したもの）
```
######**コマンドボタンの設定**
```vbnet
'Caption : ボタンに表示する名称
'Default : True (True:Enterを押すことでこのボタンをクリックしたことになる)
'Cancel  : False(True:Escを押すことでこのボタンをクリックしたことになる)
```
######**日付の増減**
```vbnet
'DateAdd(Interval,Number,Date)関数の使用
Dim MyDate As Date
MyDate = Format(Date, "yyyy/mm/dd")
MyDate = DateAdd("yyyy",  1, MyDate)    '年　+1
MyDate = DateAdd("yyyy", -1, MyDate)    '年　-1
MyDate = DateAdd("m",     1, MyDate)    '月　+1
MyDate = DateAdd("m",    -1, MyDate)    '月　-1
MyDate = DateAdd("d",     1, MyDate)    '日　+1
MyDate = DateAdd("d",    -1, MyDate)    '日　-1
```
######**ワークシートをスクロールする**
>[ワークシートの最終行、最終列を取得する](http://officetanaka.net/excel/vba/tips/tips79.htm)

######**複数のシートを参照する方法**
```vbnet
'複数のシートを同時に指定するには、 Array 関数を使います。次の使用例は、
'作業中のブックの 3 つのシートを選択します。

Sub Several()
    Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select
End Sub 
```

```
DOSのバッチファイルでカレントフォルダを取得するには？
%0   : バッチファイルのフルパス
%~d0 : %0からドライブ名だけを取り出す
%~p0 : %0からパス名だけを取り出す
%~n0 : %0からファイル名だけを取り出す
%~x0 : %0からファイル拡張子だけを取り出す
%~s0 : %0をMS-DOSの8.3形式の短いファイル名を取り出す
```

■参考サイト■

VBA基本
http://www.asahi-net.or.jp/~ef2o-inue/menu/menu04.html

ファイルの一覧を取得する
http://officetanaka.net/excel/vba/file/file07.htm