VBA Tips

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

ワークシートの最終行、最終列を取得する

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

######アクティブシートの使用領域の最終列、最終行の取得

ワークシートの最終行、最終列を取得する

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

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

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

標準モジュールに書く

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終了」プロシージャをボタン専用の終了ボタンのマクロに登録する。

######タイトルセルがダブルクリックされたら、その項目をタイトルにソートする

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文を使い、全てのワークシートに対して順番に何らかの処理をする

Dim Mysheet As Worksheet
For Each Mysheet In Worksheets
  Mysheet.Select
  With ActiveWindow
    .DisplayGridlines = False 'シートの枠線の非表示
    .DisplayHeadings  = False 'シートの行列番号の非表示
  End With
  '或いはページの印刷関連の設定とか…
Next Mysheet

######VbMsgBoxResult型
VbMsgBoxResult型の変数でMsgBox関数の戻り値を代入するようにすると、コード入力時に自動メンバー表示が有効になり、コードを効率的に記述できます。

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

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

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

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

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

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

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

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

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

######画面更新の停止・開始

Application.ScreenUpdating = False '画面更新の停止
Application.ScrennUpdating = True  '画面更新の開始

######画面アラートの停止・開始

Application.DisplayAlerts = False '画面アラートの停止
Application.DisplayAlerts = True  '画面アラートの開始

######イベントの停止・開始

Application.EnableEvents = False   'イベントの停止
Application.EnableEvents = True    'イベントの開始

######シートの保護・シートの保護の解除

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

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

ActiveSheet.Shapes("リストボックス名前").Visible = False 'リストボックスを非表示
ActiveSheet.Shapes("リストボックス名前").Visible = True  'リストボックスを表示
ActiveSheet.Shapes("図形名前").Visible = False           '図形を非表示
ActiveSheet.Shapes("図形名前").Visible = True            '図形を表示

######リスト表示対象セルがクリックされたら、リストボックスを表示する

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

######入力不可のテキストボックスの設定

'BackColor : &H8000000F&
'Locked    : True
'TabStop   : False

######日付テキストボックスの設定(IMEModeをOFFに)

'IMEMode : 2-fmIMEModeOff

######セル範囲を使い、コンボボックスを作成

'RowSource : 名称リスト（名称リストはシート上のセル範囲を名前で定義したもの）
'Style     : 2-fmStyleDropDownList　一覧からの選択だけでき、値の入力はできない
'          : 0-fmStyleDropDownCombo 一覧からの選択と値の入力ができる

######セル範囲を使い、リストボックスを作成

'ColumnCount : 2
'ColumnHeads : True (RowSourceプロパティに指定したセル範囲の1行上のセルを見出し）
'ColumnWidths: 50pt;40pt
'RowSource   : 名称リスト（名称リストはシート上のセル範囲を名前で定義したもの）

######コマンドボタンの設定

'Caption : ボタンに表示する名称
'Default : True (True:Enterを押すことでこのボタンをクリックしたことになる)
'Cancel  : False(True:Escを押すことでこのボタンをクリックしたことになる)

######日付の増減

'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

######ワークシートをスクロールする

ワークシートの最終行、最終列を取得する

######複数のシートを参照する方法

'複数のシートを同時に指定するには、 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