LoginSignup
59

More than 5 years have passed since last update.

VBA Tips

Last updated at Posted at 2013-01-01

普段仕事か勉強で使えそうな内容をメモしておきます。

ワークシートの最大行、最大列を取得する。(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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
59