ねこになりたい

おふとんと朝の別れをするのが辛い人向けです。正論で殴らず見守ってあげてください。

Excelマクロ覚書　～データ抽出編～

書斎 Excel VBA

図書館で借りてきた某書籍の覚書です。

 

 

【データを抽出する】

データを抽出するにはAutoFilterメソッドを使う。

Rangeオブジェクト.AutoFilter Field:=対象列, Criteria1:=条件

Range("D3").CurrentRegion.AutoFilter _  'A3を含む範囲(表)にオートフィルターを抽出する'
                          Field:=4, Criteria:="おれ"  '4列目が「おれ」のデータを抽出する'

【指定した順位までのデータを抽出する】

データを抽出するにはAutoFilterメソッドの引数にOperatorを設定する。

Rangeオブジェクト.AutoFilter Field:=対象列, Criteria1:=条件, Operator:=フィルターの種類

※種類

xlAnd

：抽出条件1と抽出条件2の両方を満たす
xlBottom1OItems
 ：抽出条件1で指定される項目数(低い値)
xlBottom1OPercent ：抽出条件1で指定される割合(低い値)
xlFilterCellColor ：セルの色
xlFilterDynamic ：動的フィルター
xlFilterFontColor ：フォントの色
xlFilterIcon ：フィルターアイコン
xlFilterValues ：フィルターの値
xlOr ：抽出条件1または抽出条件2のどちらかを満たす
xlTop10Items ：抽出条件1で指定される項目数(高い値)
xlTop10Percent ：抽出条件1で指定される割合(高い値) 
Range("D3").CurrentRegion.AutoFilter _  'D3を含む範囲(表)にオートフィルターを抽出する'
                          Field:=4, Criteria:=3, Operator:=xlTopItems  '4列目が上位3名のデータを抽出する'

指定した数値以上のデータの値を抽出するには、Criteriaで指定する。

Range("D3").CurrentRegion.AutoFilter _  'D3を含む範囲(表)にオートフィルターを抽出する'
                          Field:=4, Criteria:">=2000"  '4列目の値が200以上のデータを抽出する'

平均以上・平均以下で抽出するには、Criteriaで指定する。

xlFilterAboveAverage

：平均以上
xlFilterBottomAverage
 ：平均以下 
Range("D3").CurrentRegion.AutoFilter _  'D3を含む範囲(表)にオートフィルターを抽出する'
                          Field:=4, Criteria:xlFilterAboveAverage, _
 Operator:=xlFilterDynamic '平均以上のデータを抽出する'

セルの色で抽出するには、OperatorをxlFilterCellColorに指定する。

Range("D3").CurrentRegion.AutoFilter _  'D3を含む範囲(表)にオートフィルターを抽出する'
                          Field:=6, Criteria:=RGB(255, 0, 0) _
                          , Operator:=xlFilterCellColor  '6列目のセルの色が「赤」のデータを抽出する'

文字列で抽出するには、Criteria1を変更する。

Range("D3").CurrentRegion.AutoFilter _  'D3を含む範囲(表)にオートフィルターを抽出する'
                          Field:=4, Criteria:="栃木県"  '4列目が「栃木県」のデータを抽出する。'

Or条件で抽出する場合、OperatorにxlOrを指定する。

Rangeオブジェクト.AutoFilter Field:=対象列, Criteria1:=条件1, Criteria2:=条件2, Operator:=xlOr

Range("A3").CurrentRegion.AutoFilter _  'A3を含む範囲(表)にオートフィルターを抽出する'
             Field:=4, Criteria1:="東京都", Criteria2:="神奈川県" _
           , Operator:=xlOr  '4列目が「東京都」もしくは「神奈川県」で始まるデータを抽出する。'

And条件で抽出する場合、複数のAutoFilterを指定する。

Rangeオブジェクト.AutoFilter Field:=対象列, Criteria1:=条件1

With Range("A3").CurrentRegion _
     .AutoFilter Field:=4, Criterial:=">=10"
     .AutoFilter Field:=5, Criterial:="岩手県"
End With

【重複しないリストを抽出する】

重複しないリストを抽出するにはAdvancedFilterメソッドを使う。

Rangeオブジェクト.AdvancedFilter Action:=動作, CopyToRange:=コピー先, Unique:=重複

※動作

xlFilterCopy ：抽出したデータをコピーする

※コピー先

コピー先セル ：コピー先セルを指定する

※重複

True
 ：重複するデータを無視 
Range("A3:G24").CurrentRegion.AdvancedFilter _  'D3を含む範囲(表)にオートフィルターを抽出する。重複するデータはコピーしない'
                Action:=xlFilterCopy, CopyToRange:=Range("G3") _
 , Unique:=True '抽出結果をセルG3以降にコピーする'

【"年"、"月"で抽出する】

"年"、"月"で抽出するには、AutoFilterメソッドのOperator引数に値を設定する。

Rangeオブジェクト.AutoFilter Field:=対象列, Criteria1:=条件, Operator:=フィルターの種類

</tbod</tbod</tbod</tbod</tbod</tbod</tbod</tbod</tbod

：来月：今年

xlFilterTommorow
 ：明日
xlFilterToday
 ：今日
xlFilterYesterday
 ：昨日
xlFilterNextWeek
 ：来週
xlFilterThisWeek
 ：今週
xlFilterLastWeek
 ：先週
xlFilterNextMonth
 ：来月
xlFilterThisMonth
 ：今月
xlFilterLastMonth
 ：先月
xlFilterNextQuarter
 ：次の四半期
xlFilterThisQuarter
 ：今四半期
xlFilterLastQuarter
 ：前四半期
xlFilterNextYear
 ：来年
xlFilterThisYear
 ：今年
xlFilterLastYear
 ：前年
xlFilterYearToDate
 ：今年の初めから今日まで 
Range("A3:G24").CurrentRegion.AutoFilter _  'D3を含む範囲(表)にオートフィルターを抽出する。'
                Field:=1, Criterial:=xlFilterThisYear _
 , Operator:=xlFilterDynamic '1列目が「今年」のデータを抽出する'

【オートフィルターを解除する】

すべてのデータを表示するには、ShowAllDataメソッドを使う。

Worksheetオブジェクト.ShowAllData

Worksheets("Sheet1").ShowAllData  'すべてのデータを表示する'

Excelマクロ覚書　～データ並べ替え編～

書籍 Excel VBA

図書館で借りてきた某書籍の覚書です。

 

 

【並び替える】

並び替えの設定を行うには、Sortオブジェクトを使う
それぞれメソッドもしくはプロパティを指定する。

Sort.設定

※設定(メソッド)

Apply

：並べ替えを実行する。
SetRange ：並べ替えるセルを指定する。

 

※設定(プロパティ)

Header

：最初の行にヘッダ情報が含まれるかを指定する
MatchCase

：True:大文字と小文字を区別する

   False:大文字と小文字を区別しない
Orientation

：並べ替えの方向を指定する。

　xlSortRows:行単位

   xlSortColumns:列単位
Parent ：指定されたオブジェクトの親オブジェクトを返す
Rng ：並べ替えが行われる値の範囲を返す
SortFields ：並べ替えフィールドの集合を表すSort Fieldコレクションを取得する
SortMethod ：日本語の並べ替えの方法を指定する。 
With Worksheets("sheets1").Sort
     .SortFields.Clear                    'ワークシート「sheet1」に対して処理を行う'
     .SortFields.Add Key:=Range("G3"), _  'セルG3をキーに、降順で並び替える設定をする'
        SortOn:=xlSortOnValues, _
        Order:=xlDescending
     .SetRange Range("A3:G8")             'セル範囲A3～G8を並び替えの対象にする'
     .Header = xlYes                      '1行目を見出し行とする'
     .Apply                               '並べ替えを実行する'
End With

読みで並び替える場合

With Worksheets("sheets1").Sort
     .SortFields.Clear                    'ワークシート「sheet1」に対して処理を行う'
     .SortFields.Add Key:=Range("A3"), _  'セルA3をキーに、降順で並び替える設定をする'
        SortOn:=xlSortOnValues, _
        Order:=xlAscending
     .SetRange Range("A3:G8")             'セル範囲A3～G8を並び替えの対象にする'
     .Header = xlYes                      '1行目を見出し行とする'
     .Apply                               '並べ替えを実行する'
End With

複数の項目で並び替える場合

With Worksheets("sheets1").Sort
     .SortFields.Clear                    'ワークシート「sheet1」に対して処理を行う'
     .SortFields.Add Key:=Range("C3"), _  'セルC3をキーに、降順で並び替える設定をする'
        SortOn:=xlSortOnValues
     .SortFields.Add Key:=Range("G3"), _  'セルG3をキーに、降順で並び替える設定をする'
        SortOn:=xlSortOnValues, _
        Order:=xlDescending
     .SetRange Range("A3:G8")             'セル範囲A3～G8を並び替えの対象にする'
     .Header = xlYes                      '1行目を見出し行とする'
     .Apply                               '並べ替えを実行する'
End With

セルの色で並び替える場合

With Worksheets("sheets1").Sort
     .SortFields.Clear                    'ワークシート「sheet1」に対して処理を行う'
     .SortFields.Add Key:=Range("F3"), _  'セルF3をキーに、降順で並び替える設定をする'
        SortOn:=xlSortOnCellColor, _
     .SetRange Range("A3:G15"), _  'セル範囲A3～A15を並べ替えの対象にする'
     .Header = xlYes                      '1行目を見出し行とする'
     .Apply                               '並べ替えを実行する'
End With

【独自のリスト順に並び替える】

並び替えの設定を行うには、Sortオブジェクトを使う
それぞれメソッドもしくはプロパティを指定する。

SortFieldコレクション.Add Key:=並べ替えのキー,
                                              SortOn:=並べ替えの種類,
                                              Order;=並べ替えの順序,
                                              CustomOrder:=ユーザー設定                         

Application.AddCustomList ListArray:=Range("A19:A22")

With Worksheets("sheets1").Sort
     .SortFields.Clear                    'ワークシート「sheet1」に対して処理を行う'
     .SortFields.Add Key:=Range("G3"), _  'セルG3をキーに、降順で並び替える設定をする'
        SortOn:=xlSortOnValues, _
        CustomOrder:=CustomListCount
     .SetRange Range("A3:G8")             'セル範囲A3～G8を並び替えの対象にする'
     .Header = xlYes                      '1行目を見出し行とする'
     .Apply                               '並べ替えを実行する'
End With

Excelマクロ覚書　～データの削除編～

書斎 Excel VBA

図書館で借りてきた某書籍の覚書です。

 

 

【空白のある行を削除する】

空白のあるセルがある行を指定して、削除すればよい。
空白のあるセルを指定するにはSpecialCellsメソッドを使う。

Rangeオブジェクト.SpecialCells(種類, オプション)

※種類

xlCellTypeComments

：コメントが含まれているセル
xlCellTypeConstants ：定数が含まれているセル
xlCellTypeFormulas ：数式が含まれているセル
xlCellTypeBlanks ：空の文字列
xlCellTypeAllFormatConditions ：表示形式が設定されているセル
xlCellTypeLastCell ：使われたセル範囲内の最後のセル
xlCellTypeVisible ：可視セル
xlCellTypeAllFormatConditons ：条件付き書式が設定されているセル
xlCellTypeSameFormatConditions ：同じ条件付き書式が設定されているセル
xlCellTypeAllValidation ：入力規則の設定が含まれているセル
xlCellTypeSameValidation ：同じ入力規則の設定が含まれているセル

 

※オプション

xlNumbers

：数値
xlTextValues ：文字
xlLogical ：論理値
xlErrors ：エラー値 
Range("D3:D15").SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'D3～D15の空白セルを取得する'

 

【重複データを削除する】

重複データを削除するには、RemoveDuplicatesプロパティを使う。

Rangeオブジェクト.RemoveDuplicate Columns:=チェックする列, Header:~タイトル行

Range("D3:D15").RemoveDuplicate Columns:=Array(1, 2, 3, 4)) 'D3～D15の表の重複データを削除する'

 

【余分な空白を削除する】

余分な空白を削除するには、Trim関数を使う。

Trim(文字列)

Dim i As String

i = 4
Do Until Range("B" & i).Value = ""
  Range("B" & i).Value = Trim(Range("B" & i).Value) 'D3～D15の表の重複データを削除する'
 i = i + 1
Loop

 

【数値のみ削除する】

指定した値が数値かどうかを判定するには、Numeric関数を使う。

isNumeric(値)

数式を含むかどうか判定するには、HasFormulaプロパティを使う。

Rangeオブジェクト.HashFormula

※種類

True

：数式を含む
False ：数式を含まない 
Dim Temp As Range  '対象セルを処理する変数「temp」を作成する。'

For Each temp In Range("A15:H25")
    If isNumeric(temp.Value) And Not temp.HasFormula Then  'チェックしているセルが「数値で」「数式を含んでない」か調べる'
       temp.Value = ""  '条件を満たす場合は、セルを空欄にする'
 End If
Next

 

Excelマクロ覚書　～データ入力・編集編～

書斎 Excel VBA

図書館で借りてきた某書籍の覚書です。

 

 

【表全体を選択する】

アクティブなセル領域を取得すれば表全体が取得できるので、
CurrentRegionプロパティを使う。

Rangeオブジェクト.CurrentRegion

Range("A3").CurrentRegion.Select
Selection.Borders.LineStyle = xlContinuous

【取得するセル範囲を変更する】

アクティブなセル領域を取得した後で、セル範囲の大きさを変更する。
セル範囲の大きさを変更するにはResizeプロパティを使う。

元のセル範囲.Resize(行数, 列数)

Range("A3").CurrentRegion.Resize(1).Interior.Color = RGB(0, 255, 255)

【新たな入力セルを選択する】

指定した方向の末端のセルを取得したい場合、Endプロパティを使う。

基準となるセル.End(方向)

末端のセル+1で新規入力用のセルが取得できる。
指定した位置から、移動した範囲を取得するにはOffsetプロパティを使う。

基準となるセル.Offset(方向)

※移動先の方向

xlDown ：舌へ
xlToLeft ：左へ
xlToRight ：右へ
xlUp ：上へ 
Range("A3").End(xlDown).Offset(1).Select  'セルA3から下方向に移動し、Offsetプロパティで1行下のセルを取得する'

【セルにフリガナを設定する】

フリガナを設定するには、SetPhoneticプロパティを使う。

Rangeオブジェクト.SetPhonetic

フリガナに設定されているテキストを取得・設定するにはTextプロパティを使う。

Phoneticオブジェクト.Text

フリガナに関する情報を取得するにはPhoneticプロパティを使う。

Rangeオブジェクト.Phonetic

Dim i As Long

i = 4
Do Until Range("B" & i).Value = ""  'B列が空欄になるまで処理を続行'
  Range("B" & i).SetPhonetic  'B列の値にフリガナを設定する'
  Range("C" & i).Value = Range("B" & i).Phonetic.Text  'C列にB列に設定したフリガナを入力する'
  i = i + 1
Loop

【ハイパーリンクを設定する】

ハイパーリンクを取得するには、Hyperlinksプロパティを使う。

Worksheetオブジェクト.Hyperlinks

ハイパーリンクを追加するには、Addメソッドを使う。

Hyperlinksオブジェクト.Add Andhor:= 対象セル, Address:= アドレス, SubAddress:= サブアドレス, TextToDisplay:= 表示テキスト

Dim i As Long

i = 4
Do Until Range("B" & i).Value = ""           'B列が空欄になるまで処理を続行'
  Worksheets("目次").Hyperlinks.Add _        'ワークシート「目次」にハイパーリンクを設定'
  Anchor:=Range("B" & i) _                   'B列にハイパーリンクを設定'
  , Address:=Range("B" & i).Value & "!A1" '実際にリンクするリンク先を各月のワークシートのセルA1にする'
  i = i + 1
Loop

【独自のリストを作成する】

独自のリストを作成するには、AddCustomListプロパティを使う。

Applicationオブジェクト.AddCustomList ListArray:=設定するリスト

Application.AddCustomList ListArray:=Range("D2:D4")           'ユーザーリストに、セルE2～E4のデータを設定する'

【連続するデータを設定する】

児童で連番を振るには、AutoFillプロパティを使う。

Rangeオブジェクト.AutoFill Destination:=セル範囲, Type:= オートフィルの種類

※オートフィルの種類

xlFillCopy

：【セルのコピー】値と形式をコピーする
xlFillSeries ：【連続データ】値(連番)をコピーする
xlFillFormats ：【書式のみコピー(フィル)】書式のみをコピーする
xlFillValues ：【書式なしコピー(フィル)】値のみをコピーする
xlFillDays ：【連続データ(日単位)】日単位でコピーする
xlFillWeekDays ：【連続データ(週日単位)】週日単位でコピーする
xlFillMonths ：【連続データ(月単位)】月単位でコピーする
xlFillYears ：【連続データ(年単位)】年単位でコピーする
xlLinearTrend ：【連続データ(加算)】連続データを加算してコピーする
xlGrowthTrend ：【連続データ(乗算)】連続データを乗算してコピーする
xlFillDefault ：【連続データ】規定値を指定してコピーする 
Range("A2").AutoFill Destination:=Range("A3:A8")  'セルA2の値を基準に、セルA3～A8の範囲に連続データでオートフィルを行う'

【リストで入力する】

入力規則の設定をするには、Validationプロパティを使う。

Rangeオブジェクト.Validation

入力規則を追加するには、Addプロパティを使う。

Validationオブジェクト.Add Type:= 入力用の種類, Formula1:=入力する値

入力規則を削除するには、Deleteプロパティを使う。

Validationオブジェクト.Delete

※オートフィルの種類

xlValidateCustom

：ユーザ設定
xlInputOnly ：すべての値
xlValidateList ：リスト
xlValidateWholeNumber ：整数
xlValidateDate ：日付
xlValidateDecimal ：小数点数
xlValidateTextLength ：文字列(長さ指定)
xlValidateTime ：時刻 
Range("B4:C8").Validation.Add _             '入力規則を設定'
 Type:=xlValidateList _                     '入力用の種類にリストを設定'
, Formula1:="川崎支店, 盛岡支点, 仙台支店"  '入力する値を設定'

【文字列の一部を取得する】

左側から指定した文字数だけ取得するには、Leftプロパティを使う。

Left(対象のデータ, 文字数)

Dim i As Long

i = 4
   Do Until Range("B"& i).Value = ""
   Len(Range("B" & i).Value, 2)  'C列(「姓」欄)に、B列のデータのうち左から2文字分のデータを入力'
   i = i + 1
Loop

指定した位置から指定した文字数だけ取得するには、Mid関数を使う。

Mid(文字列, 開始位置, 文字数)

Dim i As Long

i = 4
   Do Until Range("B"& i).Value = ""
   Mid(Range("B" & i).Value, 4, 3)  'C列(「姓」欄)に、B列のデータのうち4文字目から3文字目までのデータを入力'
   i = i + 1
Loop

【全角のカタカナに統一する】

文字列を指定した形式に変換するには、StrConv関数を使う。

StrConv(文字列,変換方法)

※変換方法の種類

xlUpperCase

：文字列を大文字に変換
xlLowerCase ：文字列を小文字に変換
xlProperCase ：文字列の各単語の先頭の文字を大文字に変換
xlWide ：文字列内の半角文字を全角文字に変換
xlNarrow ：文字列内の全角文字を半角文字に変換
xlKatakana ：文字列内のひらがなをカタカナに変換
xlHiragana ：文字列内のカタカナを平仮名に変換
xlUnicode ：文字列をUnicodeに変換 
Dim i As Long

i = 4
   Do Until Range("B"& i).Value = ""  'C列のデータが空欄になるまで処理を繰り返す'
   Range("C" & i).Value = _  ''
   StrConv(Range("C" & i).Value, vbWide)  'C列のデータを、全角に変換したデータで置き換える'
   i = i + 1
Loop

【アルファベットを大文字に統一する】

指定した文字を大文字に変換するには、Ucase関数を使う。

Ucase(文字列)

Dim i As Long

i = 4
   Do Until Range("B"& i).Value = ""
   Range("B" & i).Value = Ucase(Range("B" & i).Value)  'B列のデータを大文字に変換したデータで置き換える'
   i = i + 1
Loop

【バラバラに入力された文字を結合する】

文字列を連結するには、&演算子を使う。

文字列1 & 文字列2

Dim i As Long

i = 4
   Do Until Range("B"& i).Value = ""
   Range("D" & i).Value = Range("B" & i).Value & Range("C" & i).Value 'D列のデータをB列とC列のデータを結合した文字で入力する'
   i = i + 1
Loop

【空白の半角/全角を統一する】

指定した文字を、指定した文字に置き換えるにはReplace関数を使う。

Replace(対象, 検索文字, 置換文字)

Dim i As Long

i = 4
   Do Until Range("B"& i).Value = ""
   Range("D" & i).Value = Replace(Range("B" & i).Value, "　", " ") 'B列の半角スペースを全角スペースに置き換える'
   i = i + 1
Loop

Excelマクロ覚書　～エラー処理編～

書斎 Excel VBA

図書館で借りてきた某書籍の覚書です。

 

 

【エラーが発生したときの処理を追加する】

想定外のことがあっても処理を続けたい場合は、On Error Resume Nextステートメントを使う。
On Error Resume Nextステートメントを使うことで、エラー発生時には直下の処理を無視して次の行の処理を行う。

On Error Resume Next

Dim Sum As Long
Dim i As Long

On Error Resume Next　'エラーが発生したら、Forステートメント以下を無視する'
For i = 4 To 15
    Sum = Sum + Range("F" & i).Value
Next
MsgBox "合計額：" & Sum
End If

指定した位置に処理を移す場合は、On Error GoToステートメントを使う。

On Error GoTo 行ラベル
      処理
　  エラー発生箇所
　   Exit Sub

行ラベル：
　   エラー処理ルーチン

Dim Sum As Long
Dim i As Long

On Error GoTo ErrCd1　'エラーが発生したら、ErrCd1に処理を移す'
For i = 4 To 15
    Sum = Sum + Range("F" & i).Value
Next
MsgBox "合計額：" & Sum
Exit Sub

ErrCd1:
    MsgBox "数値以外のデータが入力されています。処理を終了します。"

エラー発生時に、エラー内容に応じた処理を適用させる場合、Errプロパティを使う。
Numberプロパティでエラー番号を取得する。

Err.Number

Dim Sum As Long
Dim i As Long

On Error GoTo ErrCd1　'エラーが発生したら、ErrCd1に処理を移す'
For i = 4 To 15
    Sum = Sum + Range("F" & i).Value
Next
MsgBox "合計額：" & Sum
Exit Sub

ErrCd1
    Select Case Err.Number
      Case 11
           Range("E" & i).Value = "0です。"
      Case 13
           Range("E" & i).Value = "数値を入力"
    End Select
    Resume Next

【コードを簡略化する】

処理をまとめる場合は、Withステートメントを使う。

With 対象
         .処理1
         .処理2
End With

With Range("A3:G3")
          .Interior.Color = RGB(0, 255, 255)
          .Font.Name = "MS Pゴシック"
　　      .Font.Size = 14
　　      .Font.Bold = True
　　      .Font.Italic = True
　　      .Borders(xlEdgeBottom).LineStyle = xlDouble
End With

Excelマクロ覚書　～条件分岐編～

書籍 Excel VBA

図書館で借りてきた某書籍の覚書です。

 

 

【条件を満たすときに処理を行う】

処理によって条件を分けるときはIfステートメントを使う。
入れ子にすることもできるので、複数の条件分岐も可能。
Endの後にIfを書き忘れないよう注意。

If 条件式 Then
   処理1
Else
   処理2
End If


If Range("G4").Value >= 70 Then  'G4セルが70以上なら'

MsgBox "合格"  　　　　　　　'「合格」と表示'
Else 　　　　　　　　　　　　　'それ以外なら'
MsgBox "不合格"　　　　　　 '「不合格」と表示'
End If

 

【条件を満たすときに処理を分岐する】

複数の分岐条件が必要な場合は、ElseIf以下に条件を記述できる。

If 条件式 Then
   処理1
ElseIf
   処理2

Else
   処理3
End If


If Range("G4").Value >= 70 Then  　　'G4セルが70以上なら'

MsgBox "合格"  　　　　　　　　　　　'「合格」と表示'

ElseIf Range("G4").Value >= 50 Then  'G4セルが50以上なら'
          MsgBox "再試" 　　　　　　 '「再試」と表示'
      Else
MsgBox "不合格"　　　　　　　　　　　'「不合格」と表示'
End If

 

【条件を満たすときに処理を分岐する２】

固定値に応じた処理を分岐する場合は、Select Caseステートメントを使う。

Select Case 条件判断の対象
Case 条件式
   処理1

Case 条件式
   処理2
Case Else
   処理3


Dim Age As Long
Age = Range("C4")
Select Case age
Case age >=30    'G4セルが30以上なら'
MsgBox "30代"
Case age >=20    'G4セルが20以上なら'
MsgBox "20代"
Case Else
MsgBox "その他"
      End Select

 

【条件が成立している間は処理を繰り返す】

条件が成立している間だけ繰り返す場合は、Forステートメントを使う。

for カウンタ変数 = 初期値 To 終了値
　繰り返す処理
Next

Dim i As Long
im Sum As Long
For i = 4 To 8
Sum = Sum + Range("C" & i) .Value
Next
MsgBox "合計額：" & Sum

 処理を強制的に抜ける場合はExitステートメントを使う。

Exit 処理対象


Dim i As Long
im Sum As Long

For i = 4 To 8
   If Range("C"&i).Value = "" Then
       MsgBox "セルC" & i"のデータが入力されていません
       Exit Sub
   End Sub
Sum = Sum + Range("C" & i) .Value
Next
MsgBox "合計額：" & Sum

 

【指定した回数だけ同じ処理を繰り返す】

処理を指定した分だけ繰り返す場合は、Do Loopステートメントを使う。

Do While 条件式
      繰り返す処理
Loop


Dim i As Long
im Sum As Long
i = 4
      Do While Range("C" & i).Value <> ""  'G列が空欄ではない間'
Sum = Sum + Range("C" & i),Value  　　　　 '合計額にCの値を加算'
Loop  　　　　　　　　　　　　　　　　　　 'ループ処理'
MsgBox "合計額：" & Sum

 

【条件が成立するまで処理を繰り返す】

条件が成立するまで処理を繰り返すには、Do Loopステートメントを使う。

Do Until 条件式
      繰り返す処理
Loop


Dim i As Long
Dim Sum As Long

i = 4 Do Until Range("C" & i).Value = "" 'C列が空欄になるまで処理を繰り返す'
 Sum = Sum + Range("C" & i).Value 　　　 'Cに値を加算'
 i = i + 1 　　　　　　　　　　　　　　　'次のセルに移動'
Loop 　　　　　　　　　　　　　　　　　　'処理をループする'
MsgBox "合計金額："& Sum

 

【オブジェクトに対して処理を繰り返す】

全てのワークシートに処理を適用させるなど、オブジェクト単位で繰り返し処理を行う場合はFor Each Nextステートメントを使う。

For Each 要素変数 In 対象
      繰り返す処理
Next


Dim i As Long
Dim wK As Worksheet

For Each wK In Worksheets     '全てのワークシートを取得する'
If wK.Name = "2019年7月" Then 'ワークシートが2019年7月であるか確認'
   wK.Activate
   MsgBox "「2019年7月」ワークシートが存在します"

   Exit Sub
   End If
Next
MsgBox "「2019年7月」ワークシートは存在しません"

Excelマクロ覚書　～変数・定数編～

書斎 Excel VBA

図書館で借りてきた某書籍の覚書です。

キャプチャ貼らないとな・・・忘れる。

 

 

【変数を使用する】

変数を宣言するには、Dimステートメントを使う。

Dim 変数名 As データ型

Dim 変数名 As データ型, 変数名 As データ型


Dim Money As Long  '「Money」変数を用意する

Money = Range("D4").Value '「Money」変数にD4の値を代入する
MsgBox "所持金：" & Money 'メッセージを表示する'

 

【オブジェクト変数を使用する】

オブジェクト変数を宣言するには、Dimステートメントを使う。

Dim オブジェクト変数名 As オブジェクト


Dim wK As Worksheet  '変数「wK」にオブジェクト変数を宣言する
Set wK = Worksheets.Add 'ワークシートを追加し、wKに代入する'
sh.Name = "新規ワークシート '追加したwKオブジェクト変数のシート名を変更する

 

【定数を使用する】

定数を宣言するには、Constステートメントを使う。

Const 定数名 As データ型


Const Tax As Currency = 0.08   '変数「wK」にオブジェクト変数を宣言する
MsgBox "消費税：" & Range("H4").Value * Tax　  'H4シートに消費税を加算してメッセージ表示する'

 

【配列を使用する】

配列を宣言するには、Dimステートメントを使う。

Dim 配列名(0から) As データ型


Dim i =Long
Dim Person As String  '配列「Person」を宣言'
For i = 0 To UBound(Person)
Person(i) = Range("B" & i + 4).Value
Next
MsgBox "氏名：" & Person(2)

 

【動的配列を使用する】

毎月データ件数が変わる場合などは、ReDimステートメントを使う

ReDim 配列名 As データ型


ReDim Person As String  '配列「Person」を宣言する'
ReDim Person(Range("B1:B8").Rows.Count - 1) As String  '配列の要素数を求める'　   Person(0) = Range("A4").Value
       Person(1) = Range("A5").Value
       Person(2) = Range("A6").Value