タグ: Excel

カテゴリー
Microsoft

【Excel VBA】重複しないリスト作成や、要素が 1 種類のみかどうかを判定するコード！

重複なしのリストは、VBA の辞書オブジェクトの Key を使用することで実現可能ですの！

今回はそのコードを書くときに使えるテクニックですわ♪

応用すれば、要素が 1 種類のみかどうかを判定するチェックにも使ますわね。

ポイント

  • objDictionary.Add Key:=keyValue, item:=itemValue という形だと、Key が重複した場合に実行時エラーとなる。
    「実行時エラー ‘457’ このキーは既にこのコレクションの要素に割り当てられています。」
  • このエラーを避けるには、objDictionary.Exists(keyValue) で重複チェックが必要。
  • objDictionary(keyValue) = itemValue と書けば、既存のキーであっても上書きするのでエラーとならない。
続きを読む “【Excel VBA】重複しないリスト作成や、要素が 1 種類のみかどうかを判定するコード！”
カテゴリー
Microsoft

【Excel VBA】配列やコレクションの定数クラスを用意するコード♪

追記。改善版を投稿しました！抽象度を上げ、よりシンプルになりましたの♪

ポイント

  • 標準モジュールでは、Const は定義できるが、Const で配列を定義できない。
  • クラスモジュールでは、Const を定義できないが、コンストラクタで値をセットしてゲッターから取り出すようにすることで Const のような配列を定義できる。
  • したがって、様々なパターンの定数を網羅するには、標準モジュールで 1 つ、クラスモジュールで 1 つの、合計 2 つの定数用ファイルを用意する必要がある。
  • Excel 2013 で試した。
  • ファイル名に「Const」が使用できないことを考慮し、「ConstM」「CstM」や、「ConstC」「CstC」といったファイル名を付けると良いのではないかと思う。

経緯

続きを読む “【Excel VBA】配列やコレクションの定数クラスを用意するコード♪”
カテゴリー
Microsoft

【Excel VBA】現在年月日時分秒文字列を yyyyMMddHHmmss 形式で得るコード♪

ポイント・気がついたこと

  • yyyy → 4桁の年
  • mm → 2桁の月
  • dd → 2桁の日にち
  • hh → 2桁の時
  • nn → 2桁の分
  • ss → 2桁の秒
  • Excel 2013 で試したが、yyyyMMddHHmmss、yyyymmddhhmmss、の結果が同じだった。最初の MM は月で、次の mm は時間。大文字小文字が変わっても、月と時間が正しく表示された。なぜ？

VBA コード

Sub Test()
    Dim strNow As String
    strNow = Format(Now, "yyyymmddhhnnss")
    Debug.Print strNow
End Sub
続きを読む “【Excel VBA】現在年月日時分秒文字列を yyyyMMddHHmmss 形式で得るコード♪”
カテゴリー
Microsoft

【Excel VBA】特定の ActiveWorkbook.Names (Names オブジェクト) を削除するコード！

の派生版ですの！

引数に Names オブジェクトの一部を渡すと、その文字列を含む Names オブジェクトをすべて削除するプロシージャを書きましたのでノートいたします♪

ActiveWorkbook.Names のポイント

  • Names オブジェクトは、セルやセル範囲に付けられる名前を指す。
  • Excel 2013 では、数式 > 名前の管理、で開いたウインドウの「名前」にあたる。
  • ActiveWorkbook.Names(1).Delete や ActiveWorkbook.Names(“名前”).Delete で削除可能
  • 名前指定で削除する場合は、シート名を含めないと一致せず、その結果削除できない場合があるので注意。
  • シート名を含めた Names オブジェクトの Name プロパティは、「シート名!名前」というフォーマットで格納されている。
続きを読む “【Excel VBA】特定の ActiveWorkbook.Names (Names オブジェクト) を削除するコード！”
カテゴリー
Microsoft

【Excel VBA】Range の情報を取得する方法整理！

ポイント

  • フィルタ結果が飛び地（歯抜け、複数のセル領域）となり、その可視部分を Range として取得した場合、複雑な結果が得られる。
  • 飛び地可視部分を取得したコードは Range.SpecialCells(xlCellTypeVisible)
  • 飛び地のセルを Range に含めた場合、それぞれのセル範囲の Range が作られる。
  • 飛び地は Areas プロパティの中に含まれるようになる。
  • Range のプロパティメンバとして Areas があり、Areas のプロパティメンバとして飛び地の Range が含まれる。
  • Areas 内の Range のインデックスは 1 から始まる。
  • 飛び地の Range の数は Areas.Count で取得できる。
  • 飛び地の Range は Range.Areas(n) で取得できる。
  • Union 関数を使用して複数の Range を統合した変数は、フィルタ結果が飛び地の可視部分の Range を取得した時の構造と同じ。

以上のことに気がついたわけですけれども、それを学習できたコードを残しておきますの♪

続きを読む “【Excel VBA】Range の情報を取得する方法整理！”
カテゴリー
Microsoft

【Excel VBA】イテレータを作って、列の選択肢を順番に選んでフィルタをかけるコード

ポイント

Iterator クラスでは、もれなく反復するために、最低次の機能があればよい。

  • HasNext 関数を用意し、次の要素があるかを判定できる機能
  • Next 関数を用意し、次の要素を取得できる機能
続きを読む “【Excel VBA】イテレータを作って、列の選択肢を順番に選んでフィルタをかけるコード”
カテゴリー
Microsoft

【Excel VBA】配列を横方向に、縦方向に、縦横範囲に 1 度の操作で書き込むコード

ポイント

  • Range オブジェクトに配列を代入すればよい。
  • Range のセル数と、配列の要素数は一致させること。
  • 1次元配列 ⇒ 横方向に書き込み。
  • 2次元配列 ⇒ 1次元目が行に、2次元目が列に書き込み。
  • 縦方向に書き込むには、1次元配列を WorksheetFunction.Transpose 関数にかけた結果を Range に代入すればよい。

横、縦、縦横方向に書き込むサンプルコード

続きを読む “【Excel VBA】配列を横方向に、縦方向に、縦横範囲に 1 度の操作で書き込むコード”
カテゴリー
Microsoft

【Excel VBA】配列の要素数に変数の数字を設定する方法

ポイント

  • Dim lngArray(lngLength ) As Long といった書き方は不可能で、コンパイルエラーとなった。
  • [コンパイルエラー: 定数式が必要です。]
  • Dim lngArray(100) As Long ならば問題ない。
  • 変数を配列の要素数に割り当てたい場合は、ReDim でサイズを指定する。

サンプルコード

Public Sub Test()
  ' 配列の要素数となる変数
  Dim lngLength As Long
  lngLength = 100
  ' 配列を宣言し、要素数に lngLength を設定
  Dim lngArray() As Long
  ReDim lngArray(lngLength)
  ' 配列の要素数を確認
  Debug.Print UBound(lngArray)
End Sub
続きを読む “【Excel VBA】配列の要素数に変数の数字を設定する方法”
カテゴリー
Microsoft

【Excel VBA】値が入力されている下端の行番号、右端の列番号を取得するコード♪

VBA コードサンプル

  • ワークシートの一番下から上方向に登って行って、入力されたセルと最初にぶつかったところを値が入力されている下端の行番号として取得している。
  • ワークシートの一番右から左方向に戻っていって、入力されたセルと最初にぶつかったところを値が入力されている右端の列番号として取得している。
Public Sub Test()
  ' 右端の列番号を取得
  Dim lngMaxCol As Long
  lngMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Debug.Print lngMaxCol

  ' 下端の行番号を取得
  Dim lngMaxRow As Long
  lngMaxRow = Cells(Rows.Count, 1).End(xlUp).Row
  Debug.Print lngMaxRow
End Sub
続きを読む “【Excel VBA】値が入力されている下端の行番号、右端の列番号を取得するコード♪”
カテゴリー
Microsoft

【Excel VBA】クラスの雛形

VBA クラスの雛形のポイント

  • mstrClassName はログ出力用にすべてのクラスに付けている。
  • コメントは、C# のドキュメントコメントに倣っている。正しいかどうかは不明
  • クラス名を、TypeName(Me) で取得し、コンストラクタでプロパティにセットしている。
  • 完全コンストラクタを意識する場合、セッターは使用しないが書いておく。
  • コンストラクタに引数を持たせることができないため、Init という名前の関数をコンストラクタの代わりとして使う。
続きを読む “【Excel VBA】クラスの雛形”