タグ: Excel

カテゴリー
Microsoft

【Excel VBA】年月の文字列から来月の月数字、今月末の日にちを取得するコード！

やりたいこと

  • 12月の翌月は1月だが、この数字を簡単に VBA で取得したい。
  • 月によって最終日が異なるが、月末日の数字を簡単に VBA で取得したい。

ポイント

  • DateSerial メソッドを使用する。引数は、年、月、日、の数字。
  • DateSerial メソッドは 1〜12以外の月数字や、1〜28、29、30、31以外の日にち数字とした場合、はみ出た分を自動的に加減して計算してくれる。
  • 例えば、DateSerial(2015, 14, 0) ⇒ 2016/01/31 (14月 → 翌月2月 & 0日 → 前日末日) となる。
  • 来月の月数字 (y は年の4桁文字列、m は月の数字文字列) : month(DateSerial(y, m + 1, 1))
  • 今月末の日にち数字 (y は年の4桁文字列、m は月の数字文字列) : day(DateSerial(y, m + 1, 0))

VBA コード

続きを読む “【Excel VBA】年月の文字列から来月の月数字、今月末の日にちを取得するコード！”
カテゴリー
コンピューター

【Excel VBA】【改善2】座標でなく、フォーマットシートに設定したキーに対応する場所に書き込むクラスの仕様変更とリファクタリング♪

ポイント

続きを読む “【Excel VBA】【改善2】座標でなく、フォーマットシートに設定したキーに対応する場所に書き込むクラスの仕様変更とリファクタリング♪”
カテゴリー
Microsoft

【Excel VBA】【改善】フォーマットワークシートに指定した変数に対応する場所に書き込むクラスのリファクタリング・機能追加いたしました！

追記: 更に改善版です！

フォーマットに指定した変数に対応する場所に書き込むことでセルの位置、つまり座標を意識しないでよくなるクラスを書きました。

抽象度が高まり、よいと存じます。

今回は、これを改善いたしましたので、それを記録いたします♪

ポイント

  • 利用者が誤ってテンプレートの変数を削除した場合でもエラーとさせたくない。そのために、CellValues に設定していたキーに対応するテンプレートになくても無視するようにした。
  • 書き込み対象ワークシートに書き込んだ後、書き込み先範囲を移動するメソッドを追加した。これにより、ループを使って同じフォーマットの複数の帳票を作成できる。
    • WriteToVariableAndOffset → オフセット移動
    • WriteToVariableAndDown → 下に移動
  • プロパティにしなくとも問題ないものはプロパティから削除し、スリム化した。
  • 省略しても可読性が維持できると思った部分は短く書き、行数を短くした。

次の点は、あえて対応いたしませんでした。

  • フォーマットの変数に対応する値がコードに設定されていない場合は、テンプレートの変数がそのまま書き込み先シートに書き込まれてしまう。
  • このとき、特にエラーは発生しない。
  • コード側でのフォーマット変数定義漏れに気がつくチャンスが増えるメリットがある。
  • また、コード側で値が取得できないことがあると予めわかっている場合は、空文字で初期化しておけば良い。
  • よって、特に対処しない。
続きを読む “【Excel VBA】【改善】フォーマットワークシートに指定した変数に対応する場所に書き込むクラスのリファクタリング・機能追加いたしました！”
カテゴリー
Microsoft

【Excel VBA】セル番号などの絶対座標指定ではなく、セルに変数として指定した文字列に対応した場所に書き込む変数指定で帳票を楽に作れるようにするクラス！

追記: 改善版です！

なぜ実現したかったのか？

  • エクセル方眼紙での帳票は、ちょっとデザイン変更すると簡単に絶対座標がずれてしまい、VBA の座標修正が辛い。
  • ExcelCreator のように、ワークシートに変数として入力したセルの内容を書き換えて出力したら楽になると考えた。
  • セルの座標を気にする必要がなくなることがメリット
    • テンプレートのワークシートのデータを反映したいセルに変数を書き込めば良い。座標は気にしなくて良い。
    • VBA ではセルの変数に対してデータを差し込めば良い。座標を指定しなくて良い。
    • セルの名前を使う方法もある。ただし、セルの名前は扱いやすい形でワークシートに表示することができず、辛い。

処理の流れ

ワークシートテンプレートのセルに記入する変数と、その座標と、VBA に書くワークシートテンプレートの変数名と、代入する値をどうやって紐付けるかが重要です。

次に挙げるポイントの考えでつなげるようにいたしました。

ポイント

  • テンプレートの変数をキーに、そこに対応するワークシートの座標を値にして VBA で取り込んでディクショナリ 1 を作る。
  • テンプレートの変数をキーに、そこに当てはめる値を値にしてのディクショナリ 2 を作る。
  • ディクショナリ 2 をループして、テンプレートの変数を仲立ちにして、変数に当てはめる値と、ワークシートの場所をひも付け。
  • ワークシートへの書き込みは、2次元配列を Range に代入して 1 回で行う。速さを保つ。

もう少し具体的にしますと、次のようになります。

続きを読む “【Excel VBA】セル番号などの絶対座標指定ではなく、セルに変数として指定した文字列に対応した場所に書き込む変数指定で帳票を楽に作れるようにするクラス！”
カテゴリー
Microsoft

【Excel VBA】CSV ファイルをワークシートにインポートするクラスのコード♪

CSV ファイルのをエクセルにインポートする条件

  • CSV ファイルを VBA からエクセルにインポートしたい。
  • CSV の各項目にダブルクオーテーションが付いていてもいなくても、インポートできるようにしたい。
  • 読み込み先エクセルの列のデータ型、つまり表示形式を指定したい (先頭の 0 が自動的に除去されないように、などしたい) 。

読み込まれる CSV ファイル

No,氏名,電話番号
1,伊藤健一,0112223333
2,高橋哲也,0114445555
3,佐藤誠,0116667777

VBA コード

  • クエリーテーブルを使用する方法で CSV をインポートしている。
  • クエリーテーブルを使用してインポートする場合、次の副作用、想定外の出来事、悪影響が発生する。
    • インポート対象 CSV ファイルとのコネクションが張られてしまう (エクセルで、データ > 接続、で確認可能のはず) 。これにより、エクセルに読み込んだデータを編集すると元データも同期されてしまう。
    • インポート範囲を対象に Name オブジェクトが作成されてしまう (エクセルので、数式 > 名前の管理、で確認可能) 。これといって害は無いと思う。
  • 副作用を取り除くために、インポート処理完了後に次の処理を行った。
    • コネクションはすべて削除するようにした。
    • Name オブジェクトは自動的に作成されたものを狙って削除するようにした。他にもセル範囲に名前をつけた Name オブジェクトが予め存在していたため。
  • 自動生成の Name オブジェクトは、名前を指定しても連続して CSV インポートを行うと接尾辞が付与された Name オブジェクトが追加されてしまう。これにより、指定した文字列で Name オブジェクトを削除できない場合がある。
  • そこで Name オブジェクトが生成されるときに接尾辞を予め付与することで自動付与の接尾辞を抑制した。接尾辞のフォーマットは、他の Name オブジェクトと重複しないよう yyyymmddhhnnss とした。
  • データの先頭に 0 がある場合、取り除かれてインポートされてしまう。そこで、TextFileColumnDataTypes で各列に適用されるデータ型を指定できるようにしている。
  • クエリーテーブルの各プロパティについて、必須かどうか、デフォルト値は何か、は調べていない。省略できるプロパティはあると思う。
  • TextFilePlatform の値は 932 で、これはマクロの記録で設定された値をそのまま使用している。公式ドキュメントのページを見ても、932 に該当する値は記載されていない。しかし次のページを見てみると正しい値と思うのでこのまま使用する。
続きを読む “【Excel VBA】CSV ファイルをワークシートにインポートするクラスのコード♪”
カテゴリー
Microsoft

【Excel VBA】コレクションを配列に変換するコード

VBA コード

  • CollectionToArrary の返却値は Variant だが、本当はどんな型なのか限定させたかった。。。
  • 汎用性を考慮して、また、Arrary(“い”, “ろ”, “は”) などで配列を生成するときは Variant の変数とすることを考慮して、CollectionToArrary の返却値は Variant に決定した。
  • ループ内で ReDim して配列サイズを毎回変更することはしないようにした。
  • 要素を回すのに、元となるコレクションをループする方法と、作られる配列をループする方法とがある。今回、コレクションをループする方法を採った。
    • コレクションのインデックスは必ず 1 から始まる。配列のインデックスの開始は配列の定義時に自由に設定できる。
    • 配列をループしてそのインデックスに対応するコレクションのインデックスを計算して取り出すよりも、コレクションをループして配列の開始インデックスをインクリメントしていく形で要素を詰め込んでいく方がシンプルに書ける。
    • よって、ループする前に LBound で配列の最低のインデックスを取得して変数に代入しておき、ループ内の最後で変数を + 1 することで配列の添字を動かしていく。
続きを読む “【Excel VBA】コレクションを配列に変換するコード”
カテゴリー
Microsoft

【Excel VBA】ワークシートの入力内容をすべて削除してリセットするコード♪

ポイント

  • Range.Clear メソッドは次のメソッドの内容を含む。
    • Range.ClearComments メソッド → コメント
    • Range.ClearNotes メソッド → コメント
    • Range.ClearContents メソッド → 値と数式
    • Range.ClearFormats メソッド → 書式 (色や罫線、条件付き書式など)
    • Range.ClearOutline メソッド → ハイパーリンク
  • Range.ClearNotes メソッドと Range.ClearContents メソッドの処理は同じで、コメント削除 (詳細な検証はしていない)
  • リセット対象のワークシートをアクティブにする必要はない。
  • Range.Value = "" でもリセットが可能。ただし、Range.ClearContents メソッドならば計算式も更新される一方、Range.Value = "" は対象 Range を参照している計算式が更新されない。そのため、基本的に Range.ClearContents メソッドを使うのがよい。

準備

ワークシート “Sheet1” に次の内容を書き込んだセルを用意する。

  • 文字列
  • 太字の書式付きセル
  • コメント付きセル
  • 罫線つきセル
  • ハイパーリンク付きセル
  • テキストボックス
  • 図形
  • 画像

これらをすべて VBA からまっさらに削除してリセットいたします♪

続きを読む “【Excel VBA】ワークシートの入力内容をすべて削除してリセットするコード♪”
カテゴリー
Microsoft

【Excel VBA】ワークシートに入力したマスタデータを VBA から簡単に利用できるようにしたクラスのコード！

追記。改善版を作りました♪こちらのコードの方がすっきりとしております。

エクセルワークシートにマスタデータを表現いたしました。

これを、VBA に取り込んで、VBA の他のコードから呼び出して検索して値を返すクラスを作りたいですの！

そのようにすることで、エクセルのワークシートから値を取得して何かの関数で絞り込んで、、、などと意識することなくマスタデータを取り出せるようにいたします♪

ポイント

  • マスタデータをユーザ定義型の Collection で扱おうと考えたが使えなかった。
  • そこでクラス内にマスタの項目をプロパティとして定義する。そして自分自身のクラスをインスタンス化してマスタデータをプロパティに設定することでデータを扱えるようにする。
  • 自分自身のクラスには、データ構造以外に関数も含まれる。
  • 本来であれば構造を扱うクラス、処理を扱うクラスに分けるべき。
  • しかし、VBA にはクラス宣言のコードがないため、1つのファイルに両方を持たせるためにこれらを混在させた。
続きを読む “【Excel VBA】ワークシートに入力したマスタデータを VBA から簡単に利用できるようにしたクラスのコード！”
カテゴリー
Microsoft

【Excel VBA】「実行時エラー ‘1004’: Range クラスの Select メソッドが失敗しました。」への対処！

エラー再現手順

  1. Sheet2 を表示
  2. 標準モジュールで VBA を実行
  3. Worksheets(“Sheet1”).Range(“A1”).Select で次のエラー発生

実行時エラー ‘1004’: Range クラスの Select メソッドが失敗しました。

エラー再現エクセル・VBA コード

  • エクセルにシートを2つ用意する。「Sheet1」「Sheet2」
  • VBA では、標準モジュールを挿入してそこにコードを書く。 
    Option Explicit

Sub Test()
    Worksheets("Sheet1").Range("A1").Select
End Sub

修正 VBA コード

  • Worksheets.Activate メソッドを Select の前に実行すればよい。
Option Explicit

Sub Test()
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Range("A1").Select
End Sub
続きを読む “【Excel VBA】「実行時エラー ‘1004’: Range クラスの Select メソッドが失敗しました。」への対処！”
カテゴリー
Microsoft コンピューター

【Excel VBA】文字列の Date 型への変換判定、変換、年月日時分秒の取り出しコード♪

ポイント

  • 年月日を指定せずに時分秒のみを文字列指定した場合に Date へ変換すると、年: 1899、月: 12、日: 30、となる。
  • day 関数のみ、なぜか小文字からはじまる。

VBA コード

Option Explicit

Sub test()
  Dim s As String
  s = "2015/11/23 16:43"

  ' Date 型への変換判定
  Debug.Print IsDate(s)

  ' Date 型への変換
  Dim d As Date
  d = CDate(s)

  ' Date から年月日時分秒の取り出し
  Debug.Print Year(d)
  Debug.Print Month(d)
  Debug.Print day(d)
  Debug.Print Hour(d)
  Debug.Print Minute(d)
  Debug.Print Second(d)
End Sub
続きを読む “【Excel VBA】文字列の Date 型への変換判定、変換、年月日時分秒の取り出しコード♪”