ray88’s diary

お仕事で困ったとき用の自分用の覚書

ExcelVBA

VBA クラスモジュールを応用したサンプルコード①

■サンプルコード内容の図解 ■サンプルコード クラスモジュール(Customerクラスを利用してCustomersクラスを作成) Option Explicit '顧客情報セットのコレクション Private myItems As Collection '顧客情報IDをキーとしてコレクションの要素番号を紐づける…

VBA クラスモジュール プロパティプロシージャ(Let と Get)

■クラスモジュール(Customerクラス) Option Explicit 'モジュールレベル変数を宣言 Private cID As String 'ID Private cName As String '氏名 Private cAge As Long '年齢 Private cGender As Variant '性別 'IDを設定 Public Property Let ID(ByVal vID A…

VBA クラスモジュールの作成

■クラスモジュールの作成の仕方 ■モジュール名の変更の仕方

VBA 列挙型・Enumステートメント

■列挙型 数値を列挙して独自の型のように利用できる。 定数の一種でLong型の値を割り当てることができる。 Enumステートメントを使用して定義する。 【サンプルコード①】 Option Explicit '色番号を定数化 Enum ColorNumber Red = 3 Green = 4 Yellow = 5 Bl…

Excel VBA セルの書式設定

■セルの書式設定 Sub setFormat() 'セルの書式を設定 Range("B3").NumberFormatLocal = "yyyy/mm/dd" End Sub

ExcelVBA エクセルファイルにパスワードを設定・解除する

※パスワードがかかっていないファイルに対してパスワードを送って開いてもエラーにはならない。 ■Excelファイルに読み取りパスワードを設定する 名前を付けて保存する際に、読み取りパスワードを設定します。 設定するには「SaveAs」メソッドの引数に「Passw…

Excel VBA パスワード付エクセルを開く

※パスワードのかかっていないファイルに対して実行してもエラーにはならない。 ■読み取りパスワードを指定して開く Workbooks.Openメソッドの後に、Password:=”パスワード”と指定すると、読み取りパスワードが設定されたファイルを開くことができます。 Sub …

フォルダ内のサブフォルダ一覧を出力するプログラム

Sub フォルダ内のサブフォルダ一覧を取得する() Dim objFso As Object Set objFso = CreateObject("Scripting.FileSystemObject") Dim objFolder As Object Set objFolder = objFso.GetFolder(ThisWorkbook.Path) Dim f As Object For Each f In objFolder.S…

VBA デバッグ

■ステップイン・ステップオーバー・ステップアウトの意味など ※「カーソル行の前まで実行」はVBEのデバッグ→「カーソル行の前まで実行」を選択

Excel VBA Offsetプロパティ

■指定したセルから指定した行数・列数だけ離れたセルのオブジェクトを取得するプロパティ オブジェクト.Offset( RowOffset, ColumnOffset ) ・オブジェクト:Rangeオブジェクト ・RowOffset :行方向の移動数。元のセルを0とする整数で指定 ・ColumnOffset…

Leftプロパティでセルの左端の座標を取得する

■書式:オブジェクト.Left ■以下の様に固定的に座標を取得していたとします。 Set shp = ActiveSheet.Shapes.AddLine(200, 50, 500, 50) ■Leftプロパティによって座標を取得したコードに変更 Set shp = ActiveSheet.Shapes.AddLine(Range("E2").Left, 50, Ra…

Topプロパティでセル上端の座標を取得する

■書式:オブジェクト.Top ■以下の様に固定的に座標を取得していたとします。 Set shp = ActiveSheet.Shapes.AddLine(200, 50, 500, 50) ■Topプロパティによって座標を取得したコードに変更(セルの上端に矢印線が引かれる) Set shp = ActiveSheet.Shapes.Add…

VBA 特定の名前のブックが開かれているか確認する

Sub Sample() Dim myChkBook As Workbook On Error GoTo ErrHdl Set myChkBook = Workbooks("Test.xls") MsgBox "開かれています。" Exit Sub ErrHdl: MsgBox "開かれていません。" End Sub ■ファンクション化する場合 ・ファンクションプロシージャ Functio…

VBA SchemeColorプロパティ(ForeColor以下に指定する色の色見本を作成する)

注意:セルの塗りつぶし色等で使用できるColorIndexプロパティとは色と番号の対応が異なる。 ■SchemeColorプロパティの色番号と色の対応表を作成します。 Sub schemeColorSample() Dim i As Long Sheets("Sheet2").Select For i = 1 To 80 With ActiveSheet.…

VBA RGB関数

RGB( red, green, blue)red : 赤の割合を0~255の整数で指定 green : 緑の割合を0~255の整数で指定 blue : 青の割合を0~255の整数で指定 ■使用例 dim shp as shape set shp = ActiveSheet.Sapes.Addline(200,50,500,50) shp.Line.ForeColor.RGB = RGB(255,…

Excel VBA Shapesコレクションで罫線を引く

オブジェクト.Shapes.AddLine( BeginX, BeginY, EndX, EndY )・オブジェクト:ワークシートのオブジェクトを指定 ・BeginX : 始点X座標を指定 ・BeginY : 始点Y座標を指定 ・EndY :終点X座標を指定 ・EndY : 終点Y座標を指定 ActiveSheet.Shapes.AddLine(200…

VBA フォルダの存在確認(FileSystemObject)

■呼び出し先プロシージャ Sub test() Dim folderPath As String Dim Result As Boolean folderPath = "C:\Users\デスクトップ\テスト\テストフォルダ" If IsExistsDir(folderPath) Then MsgBox "フォルダは存在します" Else MsgBox "フォルダは存在しません"…

ExcelVBA 複数の条件に合致する行番号を取得する(2つ以上の列のセルの値に合致する行)

■参考URL 複数の条件に合う行番号を取得するには -行列 A列 B列 C列 D列- その他(Microsoft Office) | 教えて!goo Sub テスト() Dim i As Variant i = Evaluate("SUM((A1:A15=C1)*(B1:B15=D1)*ROW(A1:A15))") If i <> 0 Then MsgBox i Else MsgBox "該…

Excel VBA Findメソッドで検索する際にセル結合がある場合

■セル結合がある場合は以下2点がポイント ①セルの検索範囲を全てのセルにする(Cells にする) ②SerchorderをxlByColumnsではなく、xlByRowsにする sub main() call.Cells.Find(What:="検索値", SearchOrder:=xlByRows) end sub ■使用例 Sub FindCell() Dim…

ExcelVBA 目次

■基本文法 Offsetプロパティ CASE句 ■ファイル検索・編集 ブックの計算方法の切り替え(自動計算・手動計算) 最終行を取得するコード Excel VBA 最終列を取得する 最端セルの取得 セルの書式を設定する セル範囲の取得 コピペと値貼り付け オートフィル ソ…

Excel VBA シート内の特定の文字列のセルを検索する

検索キーワード:VBA 正規表現 ■書式: オブジェクト.Fined(What,After,LookIn,LookAt,SearchOrder, SearchDirection,MatchCase,MatchByte,SearchFormat) ※オブジェクトと各引数の説明 ・オブジェクト:検索対象となるセル範囲のRangeオブジェクトを指定 ・W…

値一致チェック

■列1の値と列2の値を比較し、列2の中で列1にない値のセルの背景に色を付ける Sub funcCheckValue(targetColumn1 As Integer, targetColumn2 As Integer, startRow As Integer) '----------------------------------------------------------------------…

特定の文字列の入ったセルのある行を削除する

■以下の表のE列のステータスが「キャンセル」のデータについて行ごと削除するとします。 ・行削除前 ・行削除後 ■上の行から削除すると変数「i」で参照する場所がどんどんズレて行くので、 最終行から順にチェックして下から削除していくのがポイント。 Sub …

他のブックの内容を取得する

Sub openLinkedBook() Dim wb As Workbook Dim targetPath As String '参照するブックのパスを設定 targetPath = "C:\Users\デスクトップ\売上確定情報.xlsx" 'リンクを更新せずにブックを開く '開こうとするブックに他のブックを参照する式があった場合のエ…

指定したフォルダ内にサブフォルダを一括作成する

■ExcelのB列3行目以降に記載したフォルダ名で親フォルダ内にサブフォルダを一括作成①ExcelのB列3行目以降に作成したいサブフォルダのフォルダ名を入力しておく。 ②ファイルダイアログでサブフォルダを作成する親フォルダを指定 ③指定したフォルダ内にサブフ…

UTF-8形式のCSVファイルをテーブルインポートできるよう加工

■UTF-8形式のままテーブルインポートしようとすると文字化けするので、ANSI形式で保存しなおす。ついでに、データの各項目にダブルクォーテーションもつける・編集前UTF-8形式のCSVファイル ・編集後UTF-8形式のCSVファイル ・インポート前テーブル ・インポ…

罫線を引く(格子・枠線・N行ごとに下線・セルの上下の値が相違する場合のみ下線を引く)

■罫線の設定 ・LineStyleプロパティ:罫線の種類(必須) ・Weightプロパティ:罫線の太さ(省略可) ・ColorIndexプロパティ:罫線の色(省略可) ■格子罫線を引く object.Borders.LineStyle = xlContinous (例) Sheet1のセル範囲B2:D5に格子罫線を引く Sub…

参照設定でチェックを入れる場所と変数宣言の仕方

■ADODBを事前バインディングする際は参照設定の 「Microsoft ActiveX Data Objects 6.1 Library」にチェックを入れる変数の宣言の仕方 Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Set CN = New ADODB.Connection Set RS = New ADODB.Recordset ■…

Dir関数(ファイルの存在確認・フォルダ内の全てのファイルを調べる)

■Dir関数 ファイルの存在確認。パスを含まないファイル名を返す。(つまり戻り値String型) Dir("ファイルパス") ・引数に指定したファイル名が存在しないと空欄を返す。 ・引数にワイルドカード「*」を指定した場合はいかのような動きをする。 ①最初にDir関…

ファイルが既に開かれているかどうか調べる

※こちらの方法もあり↓こちらの方が使い勝手良いかも。。。 Excel VBA ファイルが既に開かれているかどうか調べる その② - ray88’s diary ■使用にあたっての注意事項 たとえば、各部署ごとにフォルダが分かれていて、各同じファイル名のファイルが格納されて…