Excel VBA 使用頻度の高いメソッド・プロパティまとめ

マクロ(Excel VBA)

Excel VBAでよく使用しているメソッド・プロパティを集めてみました。

メソッド対象(図はA1)に対して命令を行う。
プロパティ:対象の値の取得・変更を行う。

スポンサーリンク

セルの操作

Cells,Range

CellsとRangeはセルの操作を行う際にどちらも使用します。まず使い方を説明します。

Cells

Cellsはセル単体を指定します。
Cells(行(縦)番号,列(横)番号)と指示します。

Cellsは位置を指定しない場合はシート全体になる。
Cells.ClearContents と実行すればシート全体のセルの値を消すことができる。

Range

Range(レンジ)はその名の通り範囲を指定するオブジェクトです。

Rangeの使いやすい所はExcelのセル参照と同じ指示で出来るところです。”A1″でA1のセルです。
”A1:F5”でA1からF5までの範囲となります。

Cell2の使い方

Cell1とCell2はCell1が左上のセルCell2が右下のセルと覚えておこう。

For~Nextで列(横)方向に変動させるならCellsが必要
Rangeのみだと列方向がアルファベットなので増減できない。
Cellsの位置指定を利用し横方向にも変動させよう。

メソッド
.Clear・・・指定範囲の初期化

.Clearは指定範囲の値やフォント設定・罫線・背景色・コメントなどを初期化できる。
部分的に初期化したい場合は下記を使用すると指定の項目を初期化できます。

  • .ClearContents・・・値の初期化
  • .ClearComments・・・コメントの初期化
  • .ClearFormats・・・書式の初期化
.Select・・・指定範囲の選択

.Selectは指定範囲の選択を行えます。

.Copy・・・指定範囲のコピー

.Copyは指定範囲をコピーできます。
個人的によく使ったのはマクロで完成した範囲を最後に.Copyして終了させてました。
そまま貼り付けができて便利です。

下記を使うとコピーと同時に貼り付けができます。
Range(“A1:F5”).Copy Range(“A11:F15”).PasteSpecial(xlPasteValues)
A1:F5を1行でA11:F15へ貼り付けています。

PasteSpecialのカッコ内で貼り付けの種類を選択します。
xlPasteValuesは値のみ貼り付けです。

プロパティ
.Value・・・値の取得・変更

.Valueはセルの値のプロパティです。一番使用頻度が高いんじゃないでしょうか。
Range(“A1”).Value = Range(“A10”).Value ・・・セルの値を別のセルへ転記
Range(“A1”).Value = 変数名       ・・・変数を特定のセルに入れる。
変数名 = Range(“A1”).Value        ・・・セルの値を変数に入れる。

.Font.Color・・・フォントの色取得・変更

.Fontはセルのフォント情報で.Colorでフォントの色プロパティです。
色の指示はRGB関数を使用して行います。RGBは(赤,緑,青)で指示します。
.Font.Color=RGB(255,0,0)・・・これでフォントが赤字になります。

RGBはExcel内のその他の色⇒ユーザー設定でも確認できます。

.Interior.Color・・・背景色取得・変更

.Interior(インテリア)はセルの背景色情報で.Colorで背景色の色プロパティです。
色の指示はRGB関数を使用して行います。RGBは(赤,緑,青)で指示します。
.Interior.Color=RGB(0,0,255)・・・これで背景色が青色になります。

塗りつぶし無しには?

.Interior.Color=xlNone・・・これで背景色が塗りつぶし無しになります。

.Borders・・・罫線(けいせん)のプロパティ

.Bordersは罫線のプロパティです。
範囲内に罫線を書くだけなら下記で可能です。
Range(“A1:F5”).Borders.LineStyle=True ・・・範囲内が格子を選んだ状態になります。

.Bodersのカッコ内を指定すると罫線の位置を指定できます。

  • .Borders(xlEdgeTop) ・・・上側
  • .Borders(xlEdgeBottom) ・・・下側
  • .Borders(xlEdgeLeft) ・・・左側
  • .Borders(xlEdgeRight) ・・・右側
  • .Borders(xlInsideVertical) ・・・内側の縦線
  • .Borders(xlInsideHorizontal) ・・・内側の横線
  • .Borders(xlDiagonalUp) ・・・左上から右下の斜め線
  • .Borders(xlDiagonalDown) ・・・左下から右上の斜め線

.Borders.LineStyle・・・罫線の種類

.Borders.LineStyleは罫線の線の種類のプロパティです。
範囲内に罫線を書くだけなら下記で可能です。
Range(“A1:F5”).Borders.LineStyle=True ・・・範囲内が格子を選んだ状態になります。
Range(“A1:F5”).Borders.LineStyle=False・・・範囲内の罫線を解除します。

線のスタイルは色々あります。Trueは通常の線を省略しています。

  • xlContinuous ・・・通常の実線です。Trueでも書けます。
  • xlDouble   ・・・二重線 
  • xlDot      ・・・点線
  • XlDash     ・・・破線
  • xlDashDot   ・・・一点鎖線
  • xlDahsDotDot ・・・二点鎖線
.Borders.Weight・・・罫線の太さ

.Borders.Weightは罫線の線の太さのプロパティです。
よく使用するのは細線と太線でしょうか。
.Borders.Weight=xlThin・・・細線
.Borders.Weight=xlMedium・・・太線
※罫線の種類も指定した場合はできない組み合わせが有る様です。

.Borders.Color・・・罫線の色

.Borders.Colorは罫線の色のプロパティです。
FontやInteriorと同様にRGBで設定します。
Range(“A1:F5”).Borders.Color=RGB(0,255,0)・・・範囲内の罫線を緑にします。

With~End Withを使おう

罫線の設定が1行で表示できない場合はWithを使用すると見易くなります。

ファイルの操作

ExcelのマクロはExcel内の操作のみでなくフォルダを増やしたりファイルをコピーしたりもできます。
これらを使用すると毎回時間をかけてやっていた作業がワンクリックで出来るようになります。

Excelの操作

プロパティ
Excelのフルネームを取得

ThisWorkBook.FullName・・・使用しているExcelの位置とExcel名込みの情報を取得

Excelの位置を取得

ThisWorkBook.Path・・・使用しているExcelの位置を取得

Excel名を取得

ThisWorkBook.Name・・・使用しているExcel名を取得

メソッド
Workbooks.Open

Workbooks.Open(フルパスを指示)で別のExcelを開くことが出来ます。
Workbooks.Open (“D:\Excel\VBA\VBAサンプル2.xlsm”)
このような形で実際にExcelが保存位置を指定して起動するとファイルを開けます。

Workbooks.Close

Workbooks(“ファイル名”).Closeでファイルを閉じることが出来ます。
Workbooks(“ファイル名”).Close False と後ろにFalseを付けると保存せずに閉じれます。

実際どう使う?

別のExcelを開いて操作して最後に閉じるような使い方が多いと思います。
別のExcelの操作はWorkbooks(“ファイル名”).WorkSheets(“シート名”)で出来ますが変数でWorkbookのオブジェクトを宣言し変数に入れて使用するとシンプルになります。オブジェクトに変数を入れる場合は格納時に手前にsetを書きます。
下記がサンプルです。

Dim TagetExcel As Workbook
Dim Path As String
Dim TargetValue As String
Path = “D:\Excel\VBA”
Set TagetExcel = Workbooks.Open(Path & “\” & “VBAサンプル2.xlsm”)’開くExcelを変数に格納
TargetValue = TagetExcel.Worksheets(“Sheet1”).Range(“A1”).Value’別Excelのセル情報を取得
TagetExcel.Close False’保存せずに閉じる

フォルダの作成

MkDirステートメントを使用してExcelから指定の位置にフォルダを作成出来る。

Dim Path As String
Path = ThisWorkbook.Path
MkDir Path & “\” & “NewFolder1”

これでExcelを開いている位置にNewFolder1というフォルダが出来ます。

すでにフォルダがある場合はエラーが出ます。
下記のようにするとフォルダがない時だけ作成することが出来ます。

If Dir(Path & “\” & “NewFolder1”, vbDirectory) = “” Then
  MkDir Path & “\” & “NewFolder1”
End If

ファイルのコピー

FileCopyステートメントを使用してExcelから指定の位置から指定の位置にファイルをコピーできます。
FileCopy コピー元のFullName , コピー先のFullName

下記はフォルダの作成の際にで作成したフォルダにExcelをコピーしています。
FileCopy “D:\Excel\VBA\VBAサンプル2.xlsm”, “D:\Excel\VBA\NewFolder1\VBAサンプル2.xlsm”

コピー先のファイル名は好きに変えることが出来ます。

実際どう使う?

例えばファイル名を変えながら1つのファイルをコピーしていく際に大幅に時間短縮が可能です。

このようなリストが有りファイル名に【くぅま】ファイル名.xlsxという風に増やす場合、まずコピー元のファイルを【Name】ファイル名.xlsxとします。
(※ファイル名は実際のファイル名)

Dim i As Long
Dim Path As String
Path = ThisWorkbook.Path
Dim BeforeName As String
Dim AfterName As String
BeforeName = “【Name】ファイル名.xlsx”
If Dir(Path & “\” & “NewFolder1”, vbDirectory) = “” Then
   MkDir Path & “\” & “NewFolder1”
End If
For i = 2 To 5
  AfterName = Replace(BeforeName, “Name”, Range(“A” & i).Value)
  FileCopy Path & “\” & BeforeName, Path & “\NewFolder1\” & AfterName
Next i

このような形にファイルを増やすことが可能です。

VBAのReplace関数は特定の文字を置き換えることが出来ます。
Replace(文字列,検索文字,置き換え文字)
文字列内の検索文字を置き換え文字に変更することが出来ます。
検索文字が文字列内になければ何も変わりません。

上記のプログラムでは変更前ファイル名のNameをA列をFor~Nextでループさせてファイル名を変えながらコピーしています。

最後に

最後まで閲覧ありがとうございました!
たくさん有りすぎて載せきれない内容も有りますが、この内容だけでもかなり活用できると思います。
この機能ややりたいことなど要望が有りましたら、専用の記事も作成しますのでコメント・お問い合わせお願いします。

コメント

タイトルとURLをコピーしました