hakeの日記

Windows環境でプログラミングの勉強をしています。

excel

EXCEL VBAメモ - ユーザーフォーム上のTextBoxにEnter押下で連続入力する

目的 ListBoxとTextBoxがあるユーザーフォームで、TextBoxに入力したデータをListBoxに追加していきたい。 その時Enter押下だけで連続に入力していく様にしたい。 試行錯誤 通常はTextBoxにデータを入力したあとにEnterを押すとフォーカスが別のコントロール…

EXCEL VBAメモ - ユーザーフォームを常に最前面にする(Excel2016)

背景 職場のPCは現在Windows7 Excel2010の環境で統一されていて、そこで以下のようなExcelマクロを使用している。 マクロ付ブックを起動する 同時にユーザーフォームが自動で(VbModelessオプションにて)起動する 対象ブックを開く(この時ユーザーフォーム…

EXCEL VBAメモ - xlsmファイル中のマクロの書き換え

マクロ入りの雛形ファイルをコピーして作成されたExcelファイルが多数ある場合の一括マクロ更新などに使用。 参考サイト:VBAでVBEを操作する 基本的に更新対象ファイルの対象モジュールの内容を一括削除して、予め更新マクロ用Excelファイルに用意した、同…

PowerShell - Excelの操作

EXCEL用のCOMオブジェクトを作成して、あとはVBAと同じ感覚で操作できるっぽいです。 一点、配列やコレクションの括弧の形状がVBAだと( )ですが、[ ]にしないとメソッドと間違えられてエラーになるので注意。 $excel = New-Object -ComObject Excel.Applicat…

EXCEL VBAメモ - UTF-8バイト列を文字に変換する

昨日と逆の変換。 UTF-8バイト列にBOMがなくても変換される模様。 参照:Streamオブジェクト - ADOオブジェクト - MSDN Sub Sample() Dim buf(2) As Byte Dim s As String Dim i As Long buf(0) = &HE3 ' BOMなし buf(1) = &H81 buf(2) = &H82 With CreateOb…

EXCEL VBAメモ - 文字をUTF-8バイト列に変換する

ADODB.Streamオブジェクトにテキストで書き込んで、Typeプロパティをバイナリに変えてByteの配列として読み出す。 なお変換されたUTF-8バイト列にはBOMが付加されている。 参照:Streamオブジェクト - ADOオブジェクト - MSDN Sub Sample() Dim buf() As Byt…

EXCEL VBAメモ - Rangeで指定した範囲のセルの値を配列変数に

範囲が広い場合にはセルを一つずつ操作するよりも、一旦配列にコピーして操作したほうが速い(らしい)。 Sub sample() Dim r As Range, ra As Range Dim a As Variant Dim i As Long, col As Long, row As Long Set ra = Range("A1:D3") ' 初期値書き込み i…

EXCEL VBAメモ - 自作のクラスにイベントを追加する

MyClassオブジェクトのメンバ変数m_dataが5以上になるとOverflowイベントを発生する。 標準モジュールではイベント処理が記述できないので、シートモジュールかユーザーフォームで記述する。 MyClass(クラスモジュール) Private m_data As Long ' Overflow…

EXCEL VBAメモ - 他のブックのオープン・クローズの検出(Applicationイベント)

マクロが書かれているブックではないブック(エクセルファイル)のオープンやクローズをapplicationのイベントとして検出する。クローズはWorkbooks("hoge").Closeだけでなくて、ウィンドウ右上の×クリックによるクローズも検出できる。 VBEのクラスモジュー…

EXCEL VBAメモ - クリップボードのアクセス

クリップボードへのアクセスは、DataObjectを介して行う。 またクリップボードが空か否か、どんなデータが入っているかの確認はApplication.ClipboardFormatsを使用する。 データ種別を示す定数の参照先:XlClipboardFormat 列挙 (Excel) - MSDN ' クリップ…

EXCEL VBAメモ - テキストファイルアクセス

ファイルハンドル?は、#1などと番号を指定しても良いが、FreeFileで空いている番号を取得することもできる。 Sub accessTextFile() Dim fNo As Long Dim fName As String Dim s As String fNo = FreeFile fName = ThisWorkbook.Path & "\sample.txt" ' 1行…

EXCEL VBAメモ - バイナリファイルアクセス

LOFでファイルサイズを取得できる。Get,Put共、第2引数で位置を指定、第3引数のサイズ分だけ読み書きを行う。 読み取り時bufの不足分は0で埋められる。 Sub accessBinFile() Dim fNo As Long Dim fName As String Dim buf(0 To 4) As Byte Dim pos As Long, …

EXCEL VBAメモ - Windowsライブラリの機能を呼ぶ

こんなこともできるということで、各DLLにどんな機能が入っているか知ってないと使えませんが。。。 64bitと32bit環境で宣言の仕方が微妙に違うので注意。 参考 キー コード定数 (ActiveX コントロール) - MSDN Option Explicit '64bit PC Private Declare P…

EXCEL VBAメモ - エラー処理

Option Explicit Sub test() On Error GoTo ErrHandler 'エラー発生時のジャンプ先 Dim a As Long a = 1 / 0 ' エラー発生 MsgBox "エラー発生行の次へジャンプしました。" On Error GoTo 0 ' エラー処理の無効化 Exit Sub Label1: MsgBox "Label1へジャンプ…

EXCEL VBAメモ - クラスの定義と使用

メンバ変数へのアクセスはプロパティで行う。コンストラクタの引数でメンバ変数へ値を設定することはできない。検索すると裏技で対応方法がいろいろ公開されているが、後々意味が分からなくなりそうなので使用せずプロパティで行うこと。 引数の無いプロシー…

EXCEL VBAメモ - タイマー処理

Option Explicit Sub testTimer() Dim sec As Long: sec = 10 ' タイマー時間 Dim targetTime As Date ' 現在時刻 + タイマー時間 targetTime = DateAdd("s", sec, Time) Do ' セルに残り秒数を表示 Range("A1").Value = DateDiff("s", Time, targetTime) Do…

EXCEL VBAメモ - 構造体の定義と使用

Option Explicit Type MyStruct val As Long str As String End Type Sub foo() Dim a As MyStruct a.val = 10 a.str = "abcd" MsgBox a.val & a.str End Sub

EXCEL VBAメモ - 連想配列 Dictionary

Dim dic As Object Dim d As Variant ' Objectではダメ Set dic = CreateObject("Scripting.Dictionary") dic("a") = "A" ' データ追加 dic.Add "b", "B" ' データ追加、キーが存在した場合はエラー If Not dic.Exists("c") Then ' キーの存在確認 dic.Add "…

Excel - 配列数式

配列数式というのを知ったのでメモ。 対象の範囲の演算結果を内部的に配列に格納して計算を行う? で良いのかな? 下記はA1:A10の範囲の値のうち偶数をB列に表示する例。 B1からB5には以下の式が入力されている。Ctrl+Shift+Enterで入力し、式の両側に{ }が…

EXCEL VBAメモ - 正規表現

使用できる正規表現: Pattern プロパティ - VBScript - MSDN Sub foo() Dim re As Object Dim str As String Set re = CreateObject("VBScript.RegExp") str = "apple" With re .Pattern = "ap" '検索パターンを設定 .IgnoreCase = True '大文字と小文字を区…

EXCEL VBAメモ - Like演算子パターン

"文字列" Like "パターン" ? 任意の一文字 * 任意の文字 # 任意の数値 [a-z] a-z内の一文字 [!a-z] a-zに一致しない一文字

EXCEL VBAメモ - 文法(制御構文、関数)

制御文 If 条件 Then ElseIf 条件 Then Else End IfFor 変数 = 開始値 To 終了値 Step 間隔 Exit For NextFor Each 変数 in コレクション Exit For NxetDo While/Until 条件 Exit Do LoopDo Exit Do Loop While/Until 条件Select Case 変数 Case 値 Case 値 …

EXCEL VBAメモ - セルが空か否かの判断

If Range("A9") = Empty Then MsgBox "empty"

EXCEL VBAメモ - 文字と数値の変換

MsgBox Chr(65) ' -> "A" MsgBox Asc("A") ' -> 65

EXCEL VBAメモ - 描画処理無効化(処理速度の向上)

Application.ScreenUpdating = False '無効 Application.ScreenUpdating = True '有効

EXCEL VBAメモ - ファイル選択ダイアログ

Sub fileSelectDialog() Dim fn As String fn = Application.GetOpenFilename( _ "エクセルファイル(*.xls?),*.xls?" & ",CSVファイル(*.csv),*.csv") If fn <> "False" Then MsgBox fn End If End Sub

EXCEL VBAメモ - ファイル(ワークブック)のオープン&クローズ

Sub wbOpenClose() Dim wb As Workbook Workbooks.Add ' 新規ワークブック作成 Set wb = ActiveWorkbook wb.SaveAs ("test.xlsx") ' 名前を付けて保存 wb.Close ' 閉じる MsgBox "Close" Workbooks.Open "test.xlsx" ' 名前を指定して開く Set wb = ActiveWo…

EXCEL VBAメモ - パスの取得

Sub msgCurPath() MsgBox ActiveWorkbook.path ' 本ブックのあるパス MsgBox CurDir ' カレントパス(本ブックのあるパスではない) End Sub

EXCEL VBAメモ - オブジェクトの情報

Dim str As String, rng As Range, obj As Object, var As Variant MsgBox TypeName(str) 'String MsgBox TypeName(rng) 'Nothing MsgBox TypeName(obj) 'Nothing MsgBox TypeName(var) 'Empty Set rng = Range("A1") Set obj = CreateObject("Excel.Applica…

EXCEL VBAメモ - MsgBoxの第2引数

vbOKOnly 0 [OK] vbOKCancel 1 [OK] [キャンセル] vbAbortRetryIgnore 2 [中止] [再試行] [無視] vbYesNoCancel 3 [はい] [いいえ] [キャンセル] vbYesNo 4 [はい] [いいえ] vbRetryCancel 5 [再試行] [キャンセル] vbCritical 16 警告メッセージ アイコン v…