つらつら Excel VBA

私の備忘録です。

Excel ユーザー定義書式

無理矢理文字を表示させたい時 ユーザー定義書式に「@"文字"」と入力 セルに「なんたら」と入力 「なんたら文字」と表示される この場合、セル未入力だと何も表示されない 完全に非表示にしたい場合 ユーザー定義書式にセミコロンを3個入力「;;;」 何を入力…

ExcelVBA ランダム値を重複なしで取得

Dim DIC As Object, mKey Dim i As Integer, rd As Integer, no As IntegerSet DIC = CreateObject("Scripting.Dictionary")For i = 1 To 52 '連想配列の作成。 DIC.Add i, i Next For i = 52 To 1 Step -1 rd = getRandom(1, i) '1~iの範囲でランダム値を…

ExcelVBA フォームに画像ファイル表示

Dim gazou As Stringgazou = ThisWorkbook.Path & "\gif\gazou.gif" Image1.Picture = LoadPicture(gazou)

Excel VBA オートフィルタ

'オートフィルタで絞り込み、必要列だけデータを取得する。 searchData = "*条件を含む*" wsh.AutoFilterMode = False 'オートフィルタ解除 wsh.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=searchData Set targetRange = wsh.Range("A1").C…

Excel VBA Select文の使い方メモ

Select文は上から順に条件を確認し、一致したところで終わる。 基本編 Select Case aaa Case "A" Case "B" Case Else End Select 応用編 Select Case True Case aaa Like "*A*" Case aaa Like "*B" Case Else End Select

Excel VBA シートコピー失敗

wsh.Copy After:=ThisWorkbook.Worksheets(1) Excel2007以降の環境で上記のコードをExcel2003(*.xls)ファイルで実行すると失敗する。 原因 扱うことのできるExcelシートの行数、列数が異なるため。 行・列数をExcelのバージョン毎に覚えるのは嫌なので、どう…

Excel VBA ブックのパスワード解除

On Error Resume Next SendKeys "{ESC}"Dim wb As WorkbookSet wb = Workbooks.Open(fileName:=target_path, Password:="Password")If ActiveWorkbook.Name = FileName Then Debug.Print "開きました。"End If On Error GoTo 0Set wb = Nothing

Excel VBA シートの保護解除

Dim wsh As WorksheetSet wsh = ThisWorkbook.ActiveSheet If wsh.ProtectContents Then wsh.Unprotect Password:="Password" End If wsh.Protect Password:="Password" Set wsh = Nothing ※シート保護されていれば保護解除し、最後にシート保護をする。意味…

サービスを起動するバッチファイル

@echo off echo.echo ============================================================echo サービスSQLServerを起動します。echo.echo 本バッチファイルは管理者権限で実行して下さい。echo ============================================================ec…

Excel VBA セルの結合、セルの個数などの確認

Dim c As Range Set c = Thisworkbook.Worksheet("Sheet1").Range("A1") If c.MergeCells Then MsgBox "結合されています。" If c.Areas.Count = 1 And c.Count = 1 Then MsgBox "選択セルは1つです。" If c.Interior.ColorIndex >= 0 Then "背景色が設定さ…

Excel VBA 右クリックメニュー作成

■Sheet1に追加 Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myCB As CommandBar Dim myCBCtrl As CommandBarControl On Error Resume Next CommandBars("User_Short_Menu").Delete On Error GoT…

Excel VBA ファイルパスからファイル名を取り出す案

'ファイルパス、ファイル名+拡張子の状態からファイル名だけを取り出す処理。'[\]でSplitをかけて最後を取得、[.]でSplitをかけて最初を取得すればOKFunction getFileName(file_path) As String Dim temp As String Dim tmp_array2 As Variant, tmp_array1 …

Excel VBA 他ワークブックのシートを最後尾にコピー

Dim wb As Workbook, wsh As Worksheet Set wb = Workbooks(file_name)Set wsh = wb.Sheets(1) 'シート1枚目 '同じ名前のシートがあっても「シート名(2)」とかになってコピーされる。wsh.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count…

Excel VBA ディレクトリ内のファイル検索(Dir)

file_name = Dir(target_dir & "*.csv")Do While file_name <> "" file_name = Dir() '次のファイルLoop ※Dirはファイル名が返される。 ファイルが見つからない場合には空文字が返る。

Excel VBA DoEvents

OSに制御を任せる。 実行時間が長い処理を途中で停止できるようにしたかったら書く。 DoEvents

Excel VBA 関連付けられたアプリケーションでファイルを開く

Dim WSH As Object Set WSH = CreateObject("WScript.Shell")WSH.Run Chr(34) & FilePath & Chr(34) 'Set WSH = Nothing ※ファイルパスに空白が含まれていると実行に失敗します。なぜー Chr(34)でFilePathを囲み、これを回避しています。

Excel VBA InStr

If InStr(1, "C:\nantara\test.xls", "\", vbTextCompare) > 0 Then MsgBox "発見" End If ※1文字目から、比較方法はvbTextCompareで、\を探したら、3文字目でした。 見つからなかった場合は0が返ります。 検索開始場所と比較方法は省略可能。というか省略す…

Excel VBA On Error なんたらエラー処理

Sub Sample1 On Error GoTo onErr 'エラーが起きるような処理 GoTo onEnd 'Exit Subなど onErr: 'エラー時の処理を記載 Debug.Print Err.Number 'エラー番号 Debug.Print Err.Description 'エラーメッセージ Resume Next 'エラー発生個所に戻る?未検証onEnd…

Excel VBA デバッグ作業

VBEにおいてデバッグでよく使うヤツ [表示]-[ツールバー]-[編集] 主にコメント設定で使用 [表示]-[イミディエイトウィンドウ] Debug.Print の出力ウィンドウ [表示]-[ウォッチウィンドウ] 実行中の変数の中身を確認・編集ができる ショートカットキー [F5] …

Excel VBA ディレクトリ削除(RmDir)

RmDir "C:\FilePath\" ※RmDirはディレクトリ内にファイルがあるとエラーとなる。 ファイルが存在するディレクトリを削除するにはFileSystemObjectを使うようです。

Excel VBA 構造体配列

'構造体の宣言は標準モジュールのTopで行う。(Option~のすぐ下) Option Explicit Public Type FileInfo '構造体の宣言 no As Integer name As String title As StringEnd Type Public FileInfoList(10) As FileInfo '構造体配列の宣言 'こんな感じ(動作…

Excel VBA ファイル数を数える

'指定したディレクトリ内のファイル数を数える。Public Function FileCount(sDir As String) As Integer Dim rtnCnt As Integer Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") rtnCnt = FSO.GetFolder(sDir).Files.Count Set FSO…

Excel VBA 自動的に消えるメッセージボックス

Dim WSH As ObjectSet WSH = CreateObject("WScript.Shell")WSH.Popup "Message", 2, "Title", vbInformationSet WSH = Nothing ※2秒後に消えます。vbInformationは表示するアイコンです。

Excel VBA ディレクトリの存在確認

Dim tempDir As StringtempDir = ThisWorkbook.Path & "\Test\"If Dir(tempDir, vbDirectory) = "" Then MkDir tempDir 'ディレクトリの作成End If

Excel VBA フォームのモードレス表示

Public Sub UserFormShow() Dim frm As UserForm1 Set frm = New UserForm1 frm.Show vbModeless 'フォームのモードレス表示End Sub ※たくさん表示できます。収拾がつかなくならないよう注意。

Excel VBA ユーザーフォームを数える

'指定した名前のユーザーフォームを数えるFunction countUserForm(name As String) As Integer Dim cnt As Integer Dim frm As Object cnt = 0 For Each frm In UserForms If UCase(frm.Caption) = UCase(name) Then cnt = cnt + 1 End If Next countUserFor…

Excel VBA ランダム値の生成

'ランダム値を取得Function getRandom(i_min As Integer, i_max As Integer) As Integer Dim temp As Integer Dim sa As Integer '差 Randomize '乱数生成ジェネレータ '引数が逆だった場合は入れ替え。 If i_max < i_min Then temp = i_max i_max = i_min i…

Excel VBA ディレクトリの再帰検索

'引数1 targetDir 対象ディレクトリパス'引数2 extenstion 対象ファイルの拡張子を指定(例:*.csv)'引数3 recursive サブディレクトリの検索(True:行う, False:行わない) Public Sub FolderSearch(targetDir As String, extension As String, recursive As B…

Excel よく使うショートカットキー

よく使いますよねー [Ctrl]+[1] セルの書式設定 [Ctrl]+[F2] 印刷プレビュー※2007以降 [Alt]+[F11] VBE表示 [Ctrl]+[PgUp]or[PgDn] 隣のシート表示 [Ctrl]+[上]or[下]or[左]or[右] データが途切れる場所までジャンプ [Ctrl]+[:] 現在時刻 [Ctrl]+[;] 現在日付

Excel VBA Split

Dim ary_ver As Variant, i As Integerary_ver = Split("123,456,789", ",")For i = LBound(ary_ver) To UBound(ary_ver) '処理 Next

Excel VBA Excelのバージョン取得

Dim ver As Stringver = Application.Version ※「12.0」とか得られるので、文字列で取得する。 ちなみに12.0はExcel2007です。

Excel VBA 配列のループ

For i = LBound(FileList) To UBound(FileList) '処理 Next ※Lboundは配列の最小要素番号、Uboundは配列の最大要素番号を返す。 この方法だとOption Base 1などの影響を受けずに済む。

Excel VBA 動的配列

Dim var_list As Variant, cnt As Integer cnt = 0 '配列の要素追加If cnt = 0 Then ReDim var_list(0)Else ReDim Preserve var_list(UBound(var_list) + 1)End Ifvar_list(UBound(var_list)) = "データ"cnt = cnt + 1 補足 ・ReDim 配列の再定義。例)a(30,…

Excel VBA 重複なしのリスト作成

Dim DIC As Object , keys As ObjectDim i As Integer, temp as StringSet DIC = CreateObject("Scripting.Dictionary") temp = "Test" 'リストに無ければ、リストと配列に追加If Not DIC.Exists(temp) Then DIC.Add temp, temp '辞書に追加 End If '確認key…

Excel VBA シート名の重複確認

'シート名チェック処理Function checkSheetName(sheet_name As String) As Boolean Dim i As Integer Dim bln_return As Boolean bln_return = False For i = 1 To ThisWorkbook.Worksheets.Count If ThisWorkbook.Worksheets(i).Name = sheet_name Then bln…

Excel VBA ディレクトリ選択ダイアログ

'ディレクトリを選択するダイアログを表示。 Dim target_dir As String With Application.FileDialog(msoFileDialogFolderPicker) If .Show = True Then target_dir = .SelectedItems(1) End IfEnd With ※選択されなかったら空文字が返る。 取得されるパスの…

Excel VBA 処理の高速化(画面更新の停止)

例) 行や列の追加や削除を頻繁に行う場合に処理の最初と最後に以下を追加すると、すごく良い。 Application.ScreenUpdating = False '停止 Application.ScreenUpdating = True '再開 ※停止した場合は必ず再開させること。

Excel VBA 指定時刻にマクロを実行

Application.OnTime EarliestTime:=(Now + TimeValue("00:00:02")), _ Procedure:="Macro1", _LatestTime:=(Now + TimeValue("00:01:00")) ※現在時刻の2秒後にMacro1を実行する。他のマクロが実行している場合は実行できないので、1分間は様子を見る。

Excel VBA 変数宣言の強制

Option Explicit ※面倒くさがらず、最初に必ず書くこと。

Excel VBA yyyymmddhhMMss + ミリ秒

'現在の時刻をyyyymmddHHMMSS+ミリ秒で取得Function getNow() As String getNow = Format(Now, "yyyymmddHHMMSS") & getMSec()End Function 'ミリ秒を取得Function getMSec() As String Dim dblTimer As Double Dim s_return As String dblTimer = CDbl(Time…

Excel VBA 処理の停止(Sleep)

標準モジュールに記載 Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 使用方法 Sleep 1000 ※1000で1秒。1で0.001秒。

Excel VBA モジュール名

モジュール名を変更する場合、モジュール内の関数名と同じ名前は付けてはいけない。 Excel保存前は実行できるが、保存後に実行できなくなる。

ブログ公開開始

主に備忘録として、つらつら記載していきます。