色選択ダイアログの怪

セルに色を付ける処理で、あらかじめ色をユーザが設定できるようにしようと思い、Excelに組み込まれている色選択ダイアログを表示するコードをいつもの生成AIに教えてもらって書いてみた。 ところが、実行時エラーになって上手くいかない。 更に質問を重ね聞いてみるも、表示できず、エラーになる。 ネットを見ても、生成AIが教えてくれたコードとほとんど同じなので、みんなこれで表示できているように思われる。 なんでエラーになるんだろうと思いながら、ネットに出ていたあるページのコードをコピペして実行するとすんなり表示できた。 生成AIのコードと、そのコードの違いは、ダイアログを表示する関数の引数が違うだけだった。 後から、再生AIに聞いてみると、やっぱり引数を与えることで表示できるようになったという回答。 それなら、最初っからそのコードを教えてくれればいいのに...というのが、最近、生成AIを使っていてよく思うこと。

■ 上手くいくコード
  Application.Dialogs(xlDialogEditColor).Show(Index, R, G, B)

  引数
  Index     Variant  任意変更したいカラーパレットのスロット番号（1～56）
  R         Variant  任意初期色の赤成分（0～255）
  G         Variant  任意初期色の緑成分（0～255）
  B         Variant  任意初期色の青成分（0～255）
  戻り値は、ダイアログのOK（True）/ キャンセル（False）

■ ダメなコード
  Application.Dialogs(xlDialogEditColor).Show()

  引数がないと、現在アクティブなセルの背景色をセット
  戻り値は、ダイアログのOK（True）/ キャンセル（False）

--

クラスの罠

この前、書いた通りクラスを作ってレコードをDictionary（辞書）に突っ込むコード。 できたので、Dictionary からキーで引いてみると正しいレコードが引けないことが分かった。 生成AIと相談しながら、やっと解決できた。 Dictionary に突っ込むとき、インスタンスを New して突っ込んでるんだけど、その変数の定義位置が原因だった。 ダメな方のコードも生成AIに教えててもらったんだけどね。

■ ダメなコード

    Dim iRow          As Long
    Dim iLastRow      As Long
    Dim dictProduct   As Object        ' Scripting.Dictionary

    ' 辞書を作る
    Set dictProduct = CreateObject("Scripting.Dictionary")

    ' ワークシートを変数に仕舞っておく
    Set wsSource = Wb.Worksheets(1)

    iLastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    For iRow = 2 To iLastRow
        ' インスタンスを作る
        Dim recordData      As New ProductCategoryRecord

        ' A列 CONT_HIN_CD 品目コードをキーとする
        key = wsSource.Cells(iRow, 1).value

        If key = "" Then Exit For
            ' レコード情報の読み込みと構造体への格納
            recordData.ContHinCD = key                              ' CONT_HIN_CD
            recordData.ShouhinDiv = wsSource.Cells(iRow, 2).value   ' SHOHIN_DIV
            recordData.Course = wsSource.Cells(iRow, 3).value       ' COURSE
                .
                .
                .

■ おっけーなコード

    Dim iRow          As Long
    Dim iLastRow      As Long
    Dim dictProduct   As Object        ' Scripting.Dictionary
    Dim recordData    As ProductCategoryRecord

    ' 辞書を作る
    Set dictProduct = CreateObject("Scripting.Dictionary")
    
    ' ワークシートを変数に仕舞っておく
    Set wsSource = Wb.Worksheets(1)

    iLastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    For iRow = 2 To iLastRow
        ' インスタンスを作る
        Set recordData = New ProductCategoryRecord

        ' A列 CONT_HIN_CD 品目コードをキーとする
        key = wsSource.Cells(iRow, 1).value

        If key = "" Then Exit For
            ' レコード情報の読み込みと構造体への格納
            recordData.ContHinCD = key                              ' CONT_HIN_CD
            recordData.ShouhinDiv = wsSource.Cells(iRow, 2).value   ' SHOHIN_DIV
            recordData.Course = wsSource.Cells(iRow, 3).value       ' COURSE
                .
                .
                .

--

挫折（VBAからGASへのデータ連携）

VBAからメールとGoogleチャットにメッセージを飛ばすためにデータ連携をする方法を考えている。 ボクの知識だとファイルを介してのデータ連携しか思いつかなかったので、もっとスマートな方法はないものかと社内AIに聞いてみた。 そしたら、VBAからHTTPを使って、GASをWebアプリにして、doPostで送る方法を教えてくれた。 GAS側にトリガ設定をする必要もなく、全てのデータをVBAから受け取るので、GAS側でメールアドレスや、チャットのWebhook URLを管理する必要もないし、実装も割と簡単そうだったので、この方法を採用することにした。 サンプルコードをAIに教えてもらい、実行してみると、上手く行かない。 色々調べると、セキュリティの関係でプログラムからのHTTPリクエストをGASが受け取る前にGoogleさんてかWebサーバ？が破棄してることがわかった。 他にも、googleフォームを偽装して、送信する方法も教えてもらったけど、これもやっぱりHTTPなので、届かなかった。 なかなか画期的な方法だと思ったんだけど、結局使えなかった。 仕方がないので、きょうのところは、JSONにしたデータをファイルにしてGoogleドライブに置いて、GAS側は、ポーリングでファイルをスキャンして、見つけたら読み取って、内容によりメールやチャットを飛ばすことにした。 ただ、今までは、ツールごとにVBAとGASでやり取りをしていた（宛先はGAS側で管理）けど、送り先もVBAからもらうようにすることで、GAS側はどのツールからも一元的にこの送信ツールで処理できることになる。 今までよりも、ちょっとはスマートになるかも。 ここに至るまで1日掛かったけど、少しは得るものがあった。
--

Excelが落ちる件

金曜日のExcelが落ちる件、きのう、Windows11なら全部落ちるわけではなくて、上手く行くマシンもあるらしいことが分かった。 ちょっと思い当たることがあったので、修正してダメなマシンで実行してもらったのだけど、やっぱり落ちてしまう。 もう、策が思いつかないので、落ちる部分をコメントアウトして様子を見てもらうことにした。 コメントアウトすれば、動くことは、金曜日に確認しているので大丈夫だとは思うんだけど、原因がわからない。 その後、ふと思いついて社内版生成AIにコードを読ませて、現状（Windows11のマシンによって落ちる）を説明する文章を入力して問題ないかきいてみた。 そしたら、複数の答えの中に

アクティブコントロール： SetFocus メソッドは、そのコントロールをアクティブにするもので、すでにアクティブなコントロールからフォーカスを移動します。 したがって、SetFocus を呼び出す前に他のコントロールがアクティブになっていないと、エラーが発生することがあります。

というのがあった。 確かに、フォームを表示した直後にSetFocusをしている。（フォーカスを移動しているわけではない） これかもしれないと思い、きょう、時間を取ってもらい、この部分をコメントアウトして実行してもらうと上手く行った。 どうやら、こいつが原因だったらしい。 何でこんなコードを書いたのか既に覚えてないのだけど、今見るとSetFocusをする必要はない気がする。 実際、動作にも問題ない。 解決に結構時間が掛かったけど、問題が解決して良かった。 これから、SetFocusを使うときは注意することにする。
--

メニューからコマンド（関数）実行

今、作っているExcelのツール、順調に進んでいたのだけど、ちょっと問題が起きた。 起動時にフォームをモードレスで開いて、そこからオペレーションするように作っていたのだけど、Microsoft Power Automateで動かすからメニューにしてくれと言われた。 ボタンの反応が上手く取れないことがあるらしい。 そこで、メニューを追加して、そこからフォームモジュールに書いてある関数を呼ぶコードを書いたんだけど、動かない。 え？ メニューから呼ぶコードは、これまでも何度か書いてるけど、すぐ出来たような気がする。 ネットを見ても、ただ関数名を書いてあるだけで、関数がどこのモジュールにあればいいとかの記述は、見つからない。 更にググってみると、標準モジュールに書けみたいに書いてあるページを見つけた。 ホントにそうだっけ？と思い、むかし書いたコードを見てみると、メニューからは標準モジュールに書いた関数を呼んでいて、その関数のなかで、フォームモジュールの関数を呼ぶコードが書いてあった。 やっぱそうなんだ。 そんなコードを書いた記憶はなかったんだけど...。 という訳で、標準モジュールの関数を呼べば動くことがわかった。
--
のち

ソートの罠

ExcelののVBAでセル範囲をソートする処理があって、第一キーは普通に文字列で、第二キーが"※"と空白（NULL）の2つの値しかない。 普通に第一キーではソート出来るんだけど、第二キーが上手く行かない。 昇順降順のパラメータを変えてみても結果は同じ。 どうも空白だと文字コードそのものがないので比較できないのかもしれない。 苦肉の策で、ダミーで空白のセルに適当な文字を入れて、ソートしたあとその文字を消す方法で、思い通りのソート結果を得ることが出来た。
--

Pasteの罠

以前作ったツールの改修が入って、修正したのだけど、タイミングが変わったせいか『実行時エラー'1004'WorksheetクラスのPasteメソッドに失敗しました。』というエラーが頻発するようになった。 前に作った時も、同じエラーになるので、DoEventsやSleepを入れてタイミングを計ってエラーにならないようにしていた。 今回も、タイミングの値を変えて、エラーが出ないようにしたつもりだったけど、リリース後にエラーが起きるとクレームが来た。 困ったなと思いながら、ググってみると、OnErrorGotoで、エラールチンに飛ばして、そこでリカバリすればいいみたいなページを見つけた。 参考にして、エラーが起きたときに長めのSleepとDoEventsを走らせて、エラーの起きたPaste文に戻るコードを書いて実行してみると、エラーは起きてるけど、ちゃんとリカバリできてるのが分かったので、リリースした。 エラーが起きないようにするにはどうすれば良いかばかり考えていて、エラーが起きたらリカバリするって発想は無かったので、目から鱗だった。

■

昨夜、予定通り、NHKドラマ10『悪女について』後編を見る。 いろいろ謎が解き明かされるものの、富小路公子の死の真相は、謎のままで、ボク的にはちょっとモヤモヤな感じ。 原作を読めばスッキリするのかしら？ そうそう、夕方見た『相棒season17』の#2が、刑事コロンボの『パイルD-3の壁』のプロットにそっくりだった。 警察が家宅捜査をして、見つけられなかった場所に、あとで死体を遺棄するというパターン。 相棒のこの回は、前にも見た気がするけど、その時はパイルD-3の壁のパクリだとは思わなかった。
--

別の方法

Excel VBAでのCSVファイルの読込み。 QueryTables.Addで上手く行ってると思ってたのだけど、あるデータ内に改行があるとそのデータの以降のデータが読めてないとクレームが来た。 やってみると、たしかにCSVファイルには、ダブルクオートで囲まれた文字列の中に改行コードが入っているけど、読込んだExcelのセルを見ると改行以降のデータが消えている。 なんだよ、QueryTablesじゃダメなんだと思い、ネットをググってみると、 QueryTables.Add だと、改行で別レコードになるというページを見つけた。 で、そのページに

ADOを使って読み込んでみたら、セル内改行があっても正常に読み込めました。

って書いてあってURLも出ていた。 早速、コピーして試してみると、上手く行くことが分かった。 にしても、毎回CSVには悩まされるなぁ。
--
のち

別ブックに複数シートのコピー（Excel・VBA）

処理結果を複数のシートに出力した後、その結果のシートだけを別のブックに出力する処理を作っている。 複数のシートを別ブックとして出力する方法は、ネットを見ればすぐにいくつも見つかる。 例えば、こんな感じ。

Application.DisplayAlerts = False              ' メッセージボックスを非表示
ThisWorkbook.Worksheets(Array("A", "B", "C")).Copy
ActiveWorkbook.SaveAs Filename:=strFileName    ' 名前を付けて上書き保存
ActiveWorkbook.Close False                     ' 新しく作成したブックを閉じる
Application.DisplayAlerts = True               ' メッセージボックスを表示

それはいいのだけど、出来上がったブックのシートの順番が問題。 ボクは、Array("A", "B", "C") とか Array("B", "A", "C") のように Array の順番にできるのかと思っていたけど、そうではなく元のブックの順番に依ることが分かった。 Array の順がどうであろうとかわらない。 なので、元のブックのシート順を変えるか、コピー後にシートの順番を変えるしかなさそう。
--

知らないこと（Excel）

あるデータの中に同じIDが重複しているとき、重複した分を削除してユニークにする必要が出てきた。 ボクの知識だと、ループで回して、違ってたら...、同じだったら...みたいなコードしか思い浮かばないのだけど、これだとあまり芸がないのでググってみた。 そしたらRemoveDuplicatesメソッドというのが見つかった。 まさしく、やりたかったことをたった1行でやってくれる。 いまだに、知らないことがいっぱいあって（当たり前といえば当たり前なんだけど）日々、勉強せんといけないのね。
--

Dictionary（VBA）

月次のデータを顧客のDBに当てて、住所や顧客名を引くコードを書いている。 月次の件数が5万件、顧客DBが9万件強。 ExcelのシートでFor分を使って回すと埒が明かないので、一旦メモリ（2次元配列）に突っ込んで回してみたんだけど、やっぱり8分強かかってしまう。 8分でも流石に長いので、なんかいい方法はないかとネットをググっていたら、Dictionaryと云うのを見つけた。 所謂、連想配列ってやつ。 VBAで使えるのは、知らなかった。 早速、キーと値はDBのシートの行番号をDictionaryに突っ込んで実行してみた。 いままでは、For文で9万行のなかからキーを探さないといけなかったのが、Dictionaryはキーを与えれば即座にValue（値・ここではDBシートの行番号）がかえってくる。 通しで実行してみると、14秒で出力できた。 すばらしい。 いままでも同じようなコードは書いてきたけど、ここまで件数が多いことはなかったのでFor文で回していたのだけど、この方法はいいなあ。 これからは、Dictionaryを使うことにしよう。
--
のち いちじ

Excelオープン時の罠

VBAから、別ブックを読み込むとき、そのブックがリンク設定されている場合、リンクが切れているけど更新するか？みたいなメッセージが出てくることがある。 そんなことは承知の上で読み込もうとしてるので、メッセージボックスが出てきて処理を選択させるのを回避するにはどうするか調べてみた。 ブックをオープンするときの引数に『ブック内の外部参照（他のブックへの参照）とリモート参照（他のアプリケーションデータへの参照）の更新方法』を設定する引数があって、それを設定すればメッセージボックスを表示せずに読み込むことが出来ることが分かった。 良かった良かったと思っていたら、読み込んだ後、自分を再起動すると、同じようにリンクをどうする？とメッセージボックスが出てくるようになった。 困ったなあと思い、さらにググってみると、VBAで操作するのではなく、Excelの設定で対応できることが分かった。 Microsoftのページに

1. Excel で、[データ] タブの [クエリ&接続] グループで [リンクの編集] を選択します。
2. [起動時の確認] をクリックします。
3. [メッセージを表示しないで、リンクの自動更新も行わない] オプションをクリックします。

とでている。 早速やってみるとメッセージボックスは表示されずに開くようになった。
--

クラッシュ

いま、作っているExcelのVBA。 今まで作っていたツールと、ほぼ同じような動きをするので、取り敢えずそっくりコピーして、違うところを直そうと思った。 で、コピーしてVBEを開き、書き換える文字列を検索すると、『パターン文字列が不正です。』とか、『検索文字列が見つかりません。』とかってでてくる。 検索する文字列は、ソースからコピペしているので、不正だったり、無いということはあり得ない。 こんなことは、経験したことがない。 不具合が起きたら、コールドブートが鉄則なので、コールドブートしてやってみるも、結果は変わらない。 ググってみると こんなページを見つけた。 正しく、この現象だ。 フォントを変えればいいと書いてあるので、変えてみるも現象は変わらない。 そもそもSMゴシックなんか使ってないし。 ソースを全て、ただのテキストエディタにコピペして同じ検索をすると、ちゃんと検索できる（当たり前）。 そこで、新しいExcelを作って、同じ文字列を入力して、検索してみると、やっぱりちゃんと検索できる。 ボクの結論は、元のExcelファイルが壊れているというもの。 今まで作っていたツール（Excelファイル）、実際の動作は問題ないけど、ファイルのどこかが壊れているとしか思えない。 新しい空のExcelから、地道に作ることにした。
--

StrConv()の罠

今、Excelで作っているツール。 CSVファイルを読んで、ある列の全角カタカナを半角カタカナに、ハイフンなしの電話番号を市外局番、市内局番、加入者番号ごとにハイフンを挿入してExcelファイルに変換するというもの。 電話番号は、総務省の市外局番一覧をダウンロードして、番号を当てながら桁数をきめないといけないので、ちょっと大変だけど、まあ、それほど問題ではない。 問題なのは、全角カナを半角カナに変換するほう。 ネットを見るとStrConv()って関数で一発変換できそうに書いてある。 試してみると、実行時エラーになって上手く行かない。 エラーって、ただ文字列と、ナニに変換するかを指定するだけで、エラーになるような要素はない。 困ったなあと思いながらググってみると、 StrConv(string, conversion, LCID) と書いてあるページを見つけた。 LCIDってなんだと思い読んでみると

LCID
省略可能です。
使用しないと思いますので、読み飛ばして構いません。
システムとは異なる国別情報識別子 (LCID) を指定できます。
既定値はシステムが使用する LCID です。

って書いてある。 なるほど、こいつかと思い至った。 Windowsの日付の表示を米国用にしているので、 LCIDが日本ではないからエラーになってる模様。 同じページに

vbNarrow* 8* 文字列内の全角文字 (2 バイト) を半角文字 (1 バイト) に変換します。
* 国別情報の設定が中国、韓国、および日本の場合に適用されます。

とも書いてあった。 そこで、日本のLCIDを探して、引数に追加するとサクッと変換できた。 Windowsのロケールを日本以外にしてる奴なんていないかもしれないけど、もししていて、StrConv()がエラーになれば、LCIDを設定すればおっけーだからね！

■

鉄男さんの長男が16歳の誕生日だそう。 そして、きょうは、頭痛なし。
--

最小化でのメッセージBox表示

Excelでユーザーフォーム（モードレス）をUIにして、起動時にExcelそのものを、最小化して動かしていると、メッセージボックスが表示されないことに気が付いた。 因みに、起動時に最小化したからではなく、途中からでも最小化したら表示されない。 デバッグ中は、大概Excelのシートも開いたままのことが多くて、最小化していたら表示されないことに気が付かなかった。 いろいろ調べると、どうやらアプリケーション（Excel）がアクティブになっていないと表示されないことが分かった。 ということは、メッセージボックスを表示する前に、Excelをアクティブにすればいいように思える。 フォームのコントロール（ボタン）にSetFocusとかしてみたんだけどアクティブにはならない。 ググってみると、AppActivate ステートメントというのがみつかった。 ユーザーフォームのタイトルを指定してAppActivate を実行すると、ユーザーフォームがアクティブになり、思惑通りメッセージボックスも普通に表示された。 これで良さそう。
--
ときどき ときどき

いまさら？ MsgBox関数の型（Excel VBA）

普段使うMsgBoxに配置するボタンはOKだけだったりするので、あまり戻り値を気にすることはなかった。 今回は、OKとCancelを配置して、ボタン（戻り値）によって処理を振り分ける必要があり、ちょっとググってみた。 見つかったページを見ると、サンプルによって、Integer型だったりLong型だったりしている。 実際の値を見るとInteger型で十分足りそうではある。 なのに、なんでLong型で切ってるんだろう？ と釈然としないまま、さらにググっててみると、こんなページを見つけた。 結局Long型が正しいみたいだけど、VbMsgBoxResult型が正解みたい。 これからは、VbMsgBoxResult型を使うことにする。 以下、引用。

※MsgBoxの戻り値は「VbMsgBoxResult」で内部処理形式がLong型。 戻り値はLong型でも可能だがvbMsgBoxResult型を使用することでインテリセンスが利用可能となるメリットがある。

--
いちじ のち

クイックアクセスツールバー（QAT）

Excelにメニューを追加しようと思ったんだけど、トップレベルに追加することが出来ず、『アドイン』ていうメニューの下に追加されるようになっている。 因みにバージョンは『Excel for Microsoft 365』。 むかしは、出来た気がするんだけど...。 アドオン下でも別にいいんだけど、アドオンを選んでから目的のメニューを選ぶのが二度手間で、なんだか気に入らない。 ほかに方法はないかと、いろいろググっていると、クイックアクセスツールバー（QAT）というのがあるのが分かった。 保存とかアンドゥやリドゥで普通に使ってたけど、名前も知らなかったし、ここがカスタマイズできるなんて思わなかった。 というわけで、ここに自分が作ったコマンドを追加して、直接呼び出すことが出来た。 まだまだ知らないことがいろいろあるなあ。
--

CSVはQueryTables.Addで読む

毎回、苦戦するCSVの読込み。 今回は、今までExcelファイルで提供されていたのがCSVファイルに変わるのでその対応。 取り敢えず、本チャンのソフトは弄らずに、テストプログラムを作って実験することにした。 今回のCSVファイルも改行コードはLFのみ。 実データ内に改行はない。 適当にLineInputで読んでみる。 ラインフィード毎に分割して、更にカンマで分割すれば一応読み込める。 ところが、ときたま文字化けする。 そういえば、この前もなった現象。 こうなるとお手上げで、どうしようもない。 次にWorkbooks.OpenTextで読んでみることにした。 元のファイル（CSV）の拡張子をTXTに変えると、各フィールドの型を指定できるのでほぼ思いのままにデータを読み込むことが出来る。 ただ、読み込むと新しいBookが作られるので、その後、本来のbookにコピーしないといけないのが面倒臭い。 さらにネットを見ていると、QueryTables.Addを使う方法が出ていた。 これは、元の拡張子を変えなくても、各フィールドの型を指定できるし、特定のBookの特定のシートに読み込むことが出来ると書いてある。 早速試してみると、ちゃんと読み込むことが出来た。 今回はこれで行くことにする。 てか、これ覚えといて次からもCSVはQueryTables.Addで読むことにしよう。
--

謎の現象（Office365のExcelがいきなり落ちる）

数年前に作ったVBAの改修の依頼があり、動作を確認していたところ、謎の現象が出現。 取り敢えず、指摘された現象が起きるか元のまま動かしていたところ、しばらく動いたあと、何でもないと思われるところでバコッいきなり落ちる。 因みに、バコッと音はしないんだけど。 アプリが応答しないとかのメッセージもなく、何事もなかったようにいきなりディスクトップ上から消えてなくなる。 PCのせいかと思い、再起動してやってみても、結果は同じ。 ただ、落ちるタイミングは、違うところ。 数年前に作ったプログラムで、ずっと動いていたんはずで、依頼にも途中で落ちるなんてことは言われていない。 違いと言えば、Office 2019だったのが、最近Office 365に変わったこと。 困ったなと思いながら、デバッガを立ち上げブレークを張りながら実行すると、最後まで実行することが出来た。 途中、タスクマネジャでメモリの様子とかも見てみたけど、逼迫しているようでもない。 普通に動かないんじゃ、修正も出来ない。 プログラムは、まず必要なファイルをシートに読込み、それを元に印刷用のデータを200枚前後作るというもの。 ファイルを読み込み終わるまでに落ちることは無いことが分かった。 ループして、印刷用のデータを作るところの不特定なタイミングで落ちている模様。 色々考えても原因がわからない。 ネットにも、それらしい事象は出ていない。 苦肉の策で、結構時間がかかっているForループ内に、何か所かDoEventsを突っ込んでみた。 すると、落ちずに最後まで動くようになった。 制御をOSに渡さないせいで不具合が起きているんだろうね。 Office 2019のときは大丈夫だったのに...。 取り敢えず動くようになったので、修正を始めることが出来た。
--

末営業日

指定された月の末営業日を必要とする処理があり、ググってみた。 土日だけなら比較的簡単に求められるんだけど、年末のことを考えると祝祭日のリストを持たないといけないらしい。 GWの4月のことも考えるとリストを持つのが順当のよう。 面倒臭いなあ。 てか、そんなに大変なわけでもないんだけど。
--