ExcelVBAサンプルマクロ

フォルダにあるファイル名を取得する

ファイル名やフォルダ名を取得するには、Dir関数を使います。

◆Dir関数
Dir(ファイルパス,[検索の種類])

ファイルパスには、取得したいフォルダパスとファイル名を指定します。
ファイル名には、あいまい検索が使えます。
「*.xls」とすれば、拡張子が.xlsのファイルをすべて取得します。

検索の種類は必須ではないですが指定するとフォルダなどを
取得対象に含めたりできます。
フォルダ名を取得する


ファイルが存在する場合はファイル名、存在しない場合は""が返ります。

ファイルパスに指定した条件に複数ファイル該当した場合は、
まずその中の1つが取得されます。
次のファイル名を取得するには、引数なしでDir関数を実行します。

サンプルコード



Sub フォルダにあるExcelファイル名をすべて取得()

Dim strFilePath As String
Dim strFileName As String

strFilePath = ThisWorkbook.Path & "¥"
strFileName = Dir(strFilePath & "*.xls")

Do Until strFileName = ""
  MsgBox strFileName
  strFileName = Dir()
Loop

End Sub


まず、ファイルパスを指定してDir関数を実行します。

ファイル名があれば、メッセージボックスにファイル名を表示し、
再度Dir関数を引数なしで実行することで次のファイル名を取得します。

すべてのファイル名を取得した後にstrFileName = Dir()を実行すると
""が返るので、繰り返し処理を抜けます。

ExcelVBAサンプルマクロ

データ型を変換する

VBAではある程度自動的にデータ型を判別してくれますが、
自分でデータ型を指定して処理したいときはデータ型変換関数を使います。
たとえば、Split関数で区切り文字で区切った配列を作成すると、
データ型は数値でもString型になります。

そこで計算しようとすると、文字列として結合してしまいます。

サンプルコード1



Sub データ型を変換する1()

Dim ary() As String

ary = Split("1,2,3,4,5", ",")
MsgBox ary(1) + ary(2)

End Sub


「2 + 3 = 5」となってほしいのですが、Split関数の戻り値が文字列なため、
文字列の「2」と「3」を結合した「23」が表示されてしまいます。

そこで、データ型を数値型に変換して同じ処理を行います。

サンプルコード2



Sub データ型を変換する2()

Dim ary() As String

ary = Split("1,2,3,4,5", ",")
MsgBox CInt(ary(1)) + CInt(ary(2))

End Sub


書き方に注意してください。
両方とも別々にCint関数で変換してから足しています。
MsgBox CInt(ary(1) + ary(2))ではありません。

Cintは、引数の値をInterger型に変換する関数です。
このほかにも、データ型ごとに変換関数があります。

データ型変換関数内容
CByteByte型に変換
CIntInterger型に変換
CLngLong型に変換
CSngSingle型に変換
CDblDouble型に変換
CCurCurrency型に変換
CDateDate型に変換
CStrString型に変換
CBoolBoolean型に変換
CDecDecimal型に変換
CVarVariant型に変換



ExcelVBAサンプルマクロ

書式を指定して表示する

Excelでは、数値には頭には0をつけられません。
「0001」と入力すると、Excel側で自動的に0が取られてしまいます。

数字の桁をそろえて表示したい、というような場合は関数を使って
書式を指定します。

書式を指定する関数にはとFormat関数とFormat$関数があります。
Format関数は結果がVariant型で返り、Format$関数は結果が
String型で返るという違いがあります。

しかし、Variant型で返ってもFormat関数の結果は文字列しか返らないので、
まったく意味がありません。
ムダにメモリを使うだけなので、常にFormat$関数でOKです。

◆Format$関数
Format$(値,書式)

Format$関数の書式は一般機能のセルの書式設定で使われる
書式文字とほぼ同じです。

数字の頭に0をつける以外にも、日付や時間などの書式設定もできます。

サンプルコード



Sub 数字の頭に0をつけて表示()

Dim i As Long

i = 1
MsgBox Format$(i, "0000")

End Sub


ExcelVBAサンプルマクロ

数値かどうか判定する

セルの値が数値かどうかを判断するのに、VBAには
IsNumeric関数というのがありますが、これだと11,1などの数字も
数値とみなしてしまいます。

サンプルコード1



Sub セルの値が数値か判断する1()

If IsNumeric("11,1") = False Then
 MsgBox "数値ではありません。"
End If

End Sub


より厳密に判断するにはワークシート関数のIsNumber関数を使います。

サンプルコード2



Sub セルの値が数値か判断する2()

If WorksheetFunction.IsNumber("11,1") = False Then
 MsgBox "数値ではありません。"
End If

End Sub


このほかにも、IsNumeric関数は「空白のセル = 0 = 数値」と判断しますが、
IsNumber関数は「空白のセル = 数値ではない」と判断します。

状況によって使い分けるといいでしょう。

ExcelVBAサンプルマクロ

ブックを閉じるときの保存確認のメッセージを非表示にする

通常、ブックを編集した後、ブックを閉じようとすると「変更を保存しますか?」のメッセージが表示されます。

しかし、ブックを編集をしていなくても、TODAY関数など
ブックを開いただけで計算される関数を使っている場合も
メッセージが表示されます。

このメッセージを表示させなくするには、Closeメソッドの引数
SaveChangesを指定します。


内容
TRUE保存してブックを閉じる
FALSE保存しないでブックを閉じる


True、Falseいずれかを指定した場合は、
保存確認のメッセージは表示されません。


サンプルコード



Sub メッセージを表示しないでブックを閉じる()

Dim strFileName As String

strFileName = "Book1.xls"
Workbooks(strFileName).Close SaveChanges:=False

End Sub

ExcelVBAサンプルマクロ

リンクの更新などのメッセージを表示しないで開く

ブックを開くときに使うOpenメソッドには、ファイルパス以外にも
引数があります。

これらの引数を指定すると、ファイルを開く前の
データの更新メッセージを表示させないなど、
開く時の動作を指定して開くことができます。

他のブックへの参照がある場合に表示される「ほかのデータソースへのリンクが設定されています」メッセージは、
引数UpdateLinksを指定することで表示されなくなります。

UpdateLinksは以下の値のいずれかを指定します。



内容
0外部参照・リモート参照ともに更新しない
1外部参照を更新、リモート参照を更新しない
2リモート参照を更新、外部参照を更新しない
3外部参照・リモート参照ともに更新する

また、保存時の全般オプションで「読み取り専用を推奨する」にチェックが入っている場合の「保存する必要がなければ、読み取り専用で開いてください。」のメッセージは、
引数IgnoreReadOnlyRecommendedをFalseに指定することで表示されなくなります。

サンプルコード



Sub メッセージ表示しないでブックを開く()

Dim strFilePath As String
Dim strFileName As String

strFilePath = ThisWorkbook.Path & "¥"
strFileName = "Book1.xls"

Workbooks.Open _
 Filename:=strFilePath & strFileName, UpdateLinks:=1, IgnoreReadOnlyRecommended:=False

End Sub

ExcelVBAの基礎知識

配列とは

変数には様々な値が格納できますが、1つの変数には1つの値しか入れる事ができません。
沢山の値を入れたい場合、1つ1つ変数を宣言していったら大変ですね。

そこで配列というものを使います。
配列は変数が集まったものです。
配列は変数と同じく、Dimステートメントで宣言します。

◆Dimステートメント
Dim 配列名(要素数) As データ型

要素数は値を入れる場所の番号(インデックス)です。
何も指定しなければ0から始まります。
要素数の指定方法は複数あります。

◆方法1:最大要素数を指定する場合
Dim Ary(1) As Long

配列Aryを最大要素数を1にして作成します。
最大要素数が1なので、Ary(0)とAry(1)の2つの場所が作成されます。

◆方法2:要素数の最小と最大を指定する場合
Dim Ary(1 To 5) As Long

配列Aryの要素数を1〜5で作成します。
Ary(1)〜Ary(5)までの5か所に値を入れる事ができます。
最小値と最大値は、正の数ならば自由に設定できます。
0や1以外からも始める事ができます。
Dim Ary (3 To 5) As Long

この場合、Ary(3)、Ary(4)、Ary(5)の3つの場所が作成されます。

◆方法3:要素数を指定しない場合
Dim Ary() As Long

この場合、要素数0の配列を作成します。

もちろん、このままでは使えないので使うにはマクロの中で
ReDimステートメントで配列の要素数を再定義する必要があります。

このような要素数0の配列は、マクロを実行してから要素数を決めたい場合に使用します。
(シート内のデータ件数をカウントして、データ件数分の要素数をもつ配列を定義したい場合など)

こうした配列のことを動的配列といいます。

配列に値を格納する、配列から値を取り出す



配列名(インデックス)で配列のデータを指定する事ができます。
上の例で言えば、
Ary(1) = 1


とすれば配列Aryのインデックス番号1番に1を格納します。

サンプルコード


A列に日付、B列に値が入ったリストから今月分の値を、
日付ごとに値を配列aryDateに集計します。

A列   B列
2010/3/1 5
2010/3/2 2
2010/3/3 6


配列aryDateの要素数は1〜最終日の日付です。
最終日の日付は「翌月1日のシリアル番号-1」になります。

コードを書く段階では要素数が確定していません。
(要素数が実行日の最終日の日付になるため)

ですので、一度空の配列を作成し、コード内でReDimステートメントで再定義しています。


Sub 日付ごとに集計()

Dim aryDate() As Long
Dim DayCount As Long
Dim lngRow As Long
Dim i As Long

DayCount = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
ReDim aryDate(1 To DayCount)

lngRow = 1
With ThisWorkbook.Worksheets(1)
  Do Until .Cells(lngRow, "A").Value = ""
    If DateSerial(Year(Date), Month(Date), Day(.Cells(lngRow, "A").Value)) = .Cells(lngRow, "A").Value Then
      i = Day(.Cells(lngRow, "A").Value)
      aryDate(i) = aryDate(i) + .Cells(lngRow, "B").Value
    End If
    lngRow = lngRow + 1
  Loop
End With

End Sub

ステートメント

指定回数繰り返し(For...Nextステートメント)

あらかじめ繰り返す回数が決まっている場合は、
For...Nextステートメントを使います。

For カウンタ変数 = 初期値 To 終了値 [Step 増減値]
  ’処理
Next カウンタ変数


カウンタ変数というのはカウント用に使うというだけで、ただの変数です。

カウンタ変数に初期値を入れて処理を行い、「Next カウンタ変数」の
ところまできたら、カウンタ変数に増減値の値を加えて
終了値になるまで繰り返します。

増減値は省略可能で、省略した場合、1が加算されます。
マイナスを指定した場合、カウンタ変数から値を引いて繰り返します。

カウンタ変数には「i」や「n」など小文字アルファベット1文字を
使う事が多いです。

サンプルコード1


A1セル〜A10セルまで順番に連番を振ります。

Sub ForNextで繰り返し1()

Dim i As Long

With ThisWorkbook.Worksheets(1)
  For i = 1 To 10
    .Cells(i, "A").Value = i
  Next i
End With

End Sub


サンプルコード2


10行目から1行目まで下から順に行を削除していきます。

Sub ForNextで繰り返し2()

Dim i As Long

With ThisWorkbook.Worksheets(1)
  For i = 10 To 1 Step -1
    .Rows(i).Delete
  Next i
End With

End Sub


サンプルコード


繰り返すかどうかはFor...Nextステートメントの開始時に判定されます。

下記コードでは、カウンタ変数Iの値が1→4→7→10と変わり、
I = 10の時の繰り返しが終わった時点(I = 13)で繰り返し処理が
終了します。

Sub ForNextで繰り返し3()

Dim i As Long

With ThisWorkbook.Worksheets(1)
  For i = 1 To 11 Step 3
    .Cells(i, "A").Value = i
  Next i
End With

End Sub

ステートメント

条件式を使って繰り返し Do...Loop ステートメント3

先の2例では、先に条件を判断してから処理を行っています。
はじめの条件が当てはまらなかった場合は、1度も処理が
実行されません。

条件にかかわらず1回は処理を実行して、2回目以降から条件を
判定して処理を行うようにするにはUntil(While)以降を
Loopの後に持ってきます。

Do
  '繰り返したい処理
Loop Until 条件式



Do
  '繰り返したい処理
Loop While 条件式


サンプルコード



Sub DoUntilで繰り返し2()

Dim lngRow As Long

lngRow = 1

'1行目は無条件に実行、2行目以降は空白になるまで繰り返し
With ThisWorkbook.Worksheets(1)
  Do
    MsgBox .Cells(lngRow, "A").Value
    lngRow = lngRow + 1
  Loop Until .Cells(lngRow, "A").Value = ""
End With

End Sub



たいていはDo Until...Loopだけで済んでしまう事が多いので、
私はあまり使いません。

ステートメント

条件式を使って繰り返し Do...Loop ステートメント2

Do... Loopステートメントのもう1つの書き方として
「〜の間」というのがあります。

Do While 条件式
  '繰り返したい処理
Loop


次のサンプルは、前の「Do Untilで繰り返し」をDo Whileで書き直したものです。
条件式の書き方が、ちょうどDo Untilと逆になっています。

サンプル



Sub DoWhileで繰り返し()

Dim lngRow As Long

lngRow = 1

'A列が空白でなければ繰り返し
With ThisWorkbook.Worksheets(1)
  Do While .Cells(lngRow, "A").Value <> ""
    MsgBox .Cells(lngRow, "A").Value
    lngRow = lngRow + 1
  Loop
End With

End Sub


「〜の間」というのは、文章にすると回りくどい言い方で
なかなかわかりずらいと思います。

単なる書き方の問題なので、わからなかったらDo Untilを使って書けばOKです。

VBA以外のプログラム言語だとWhileを使った書き方しかなかったりするため、
どちらかというと他のプログラム言語を経験された方向けの書き方だと思います。

<<前の10件  .. 2 3 4 5 6..  次の10件>>


Powered by Seesaa
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。