普段仕事か勉強で使えそうな内容をメモしておきます。
ワークシートの最大行、最大列を取得する。(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