Softex Celware

ExcelVBA専門技術ブログ

中級者向けVBA課題 No.10 (列範囲の表示/非表示切り替えボタン)

概要

今回も「実装出来たら便利!」って問題

 

↓課題内容

 

以前の記事に似たような機能です。

中級者向けVBA課題 No.8 (大区分、小区分のある表の行表示切替システム) - Softex Celwear

セル範囲と同じサイズで配置されたボタンを押すと、そのボタンの1列目だけ表示全体表示を切り替えできるようになります。

このボタンは汎用的に利用可能で、表示切替機能を実装したい列と同じサイズで配置するだけで機能を実装可能です。

動画では2シート目には行の表示切替機能を実装しています。

 

実装のポイント

下記項目をクリアすれば同じようなものが作れます。

詳細はググるなり、今時なら生成AIに聞けば答えが出てくるので詳細の解説は行いません。

  • ボタンが配置されている範囲のセルを取得(TopLeftCell、BottomRightCell)
  • 表示状態の取得
  • 列、行の表示状態はEntireColumn,EntireRowを利用

 

コード

実装したExcelにおけるVBAのコードだけ抜粋します。  

SwitchColShowHide · GitHub

SwitchRowShowHide · GitHub

 

頂いた回答

今回はまだ回答をいただいておりません。

自ら試行錯誤した経験が自分の価値につながります!みんな頑張ってね!

 

前回課題

softex-celwear.hatenablog.com

次回課題

softex-celwear.hatenablog.com

PR

ExcelVBAでのツール開発を承っております。(対応実績350件以上 2023.8月)

お気軽にご相談ください。

coconala.com

中級者向けVBA課題 No.9 (選択セル範囲に罫線)

概要

今回は「実装出来たら便利!」って問題

 

↓課題内容

 

任意のセル範囲を選択して、見やすいように罫線を設定します。

マクロをリボンに登録しておくことで、より効率的にExcelシートの編集ができるようになります。

動画の中のマクロは2つ

  • 選択セル範囲に既定の罫線を設定する
  • 選択セル範囲の1列目で値が変化する行を区切るように罫線を設定する。

 

ちなみに罫線は

  • 外側は太い実線
  • 内側縦線は細い実線
  • 内側横線は細い点線

になっています。

 

実装のポイント

下記項目をクリアすれば同じようなものが作れます。

詳細はググるなり、今時なら生成AIに聞けば答えが出てくるので詳細の解説は行いません。

  • セル範囲の取得。選択しているものがシェイプなどセル以外のものである場合のエラーハンドリングも忘れずに
  • セルの書式設定から罫線を設定する
  • セルの値で区切り位置を判定する

 

コード

SetBorderForRibbon · GitHub

SetBorderDivideForRibbon · GitHub

 

頂いた回答

今回も回答をいただきました。毎度ありがとうございます!

 

前回課題

softex-celwear.hatenablog.com

次回課題

softex-celwear.hatenablog.com

PR

ExcelVBAでのツール開発を承っております。(対応実績350件以上 2023.8月)

お気軽にご相談ください。

中級者向けVBA課題 No.8 (大区分、小区分のある表の行表示切替システム)

概要

今回も「実装出来たら便利!」って問題

 

↓課題内容

 

ツイートの動画の中の表のように、

  • 横(列)方向が日付
  • 縦(行)方向に大区分に対して複数の小区分がある

のような場合のために有効なUIの構築例です。

 

実装のポイント

下記項目をクリアすれば同じようなものが作れます。

詳細はググるなり、今時なら生成AIに聞けば答えが出てくるので詳細の解説は行いません。

  • 特定の小区分の行(セル範囲)をまとめて「表示/非表示」を切り替える
  • セル範囲の結合はUnion関数を利用する。ちなみに結合せずに1行ずつ「表示/非表示」を行うと処理が遅くなる
  • ダブルクリックのイベントプロシージャでどの小区分がクリックされたかを判定して処理する。

 

コード

実装したExcelにおけるVBAのコードだけ抜粋します。

Mod01_メイン処理 · GitHub

Worksheet_BeforeDoubleClick · GitHub

 

頂いた回答

今回はまだ回答をいただいておりません。

自ら試行錯誤した経験が自分の価値につながります!みんな頑張ってね!

 

前回課題

softex-celwear.hatenablog.com

次回課題

softex-celwear.hatenablog.com

PR

ExcelVBAでのツール開発を承っております。(対応実績350件以上 2023.8月)

お気軽にご相談ください。

coconala.com

Excelツール「マニュアルメーカーbyE」の使い方

概要

Excelツール「マニュアルメーカーbyE」を公開します。

  • 画像を交えた自分用のメモの作成
  • 仕様書やマニュアルの作成
  • 議事録などの作成

などに役立ててください。

 

解説動画

わちょん様に動画を作成いただきました🙌 ありがとうございます!!

youtu.be

 

目次

 

ダウンロード先

drive.google.com

 

バージョンアップ情報

softex-celwear.hatenablog.com

 

マニュアル

各ボタンの機能の詳細は下記画像を参照してください。

ちなみに下記の説明図はこの「マニュアルメーカーbyE」を用いて作成しています。要した時間は90分。

 

四角形を赤枠変換

矢印を太めの赤矢印に変換

貼り付けた画像を黒枠に変換

貼り付けた画像を黒枠に変換(一括処理)

セルコピーから図形にテキスト挿入

セルコピーから図形にテキスト挿入(効率化例)

図形のテキストに数字「1,2,3」を追加

図形のテキストに丸数字「①,②,③」を追加

作図範囲の四隅に移動

シェイプを全部初期化

別名で保存

ブックのフルパス取得

図形の目次登録/登録解除

目次の移動

選択セル範囲をJPGデータで出力

選択セル範囲をJPGデータで出力(番号の自動付与)

選択セル範囲をJPGデータで出力(フルパスの自動クリップボード格納)

選択画像をJPGデータで出力

「図形」シートにあらかじめ図形保管

「図」シートは複製可能

 

イラストロジックの自動解答アルゴリズム 第2章

前回に引き続きイラストロジックの解答アルゴリズムを紹介します。

前回記事↓

softex-celwear.hatenablog.com



前回記事では1マスの解答パターンを「○」「・」「●」の3種類を分けて説明していましたが、これらの3種類の解答を数字とおいて行列演算で解答アルゴリズムを構築していきます。
行列演算を行うことでより高速に計算を処理することができます。
なぜなら、前回記事における「○」「・」「●」はそれぞれを判定するためにプログラム上では文字列操作で行う必要があり、1つ1つのマスを検証したりする必要があります。代わりに行列演算だとそれらを一括で行えたりします。
またプログラムのコード自体もより簡略化することができます。

具体的に数字に置き換えるのは次の通りです。
白マス「・」=0
未確定マス「○」=1
黒マス「●」=2

前回記事における初回解答の図1は次のようになります。

図1:初回解答

具体的に追加解答でのアルゴリズムは次のようになります。

図2:追加解答(黒マスが追加、該当数字が1つ) その1

追加解答に該当する数字3のパターン行列に計算を適応します。
解行列A、パターン行列Cとおき、CAにおいて最大値と一致する要素が有効(OK)で、それ以外を無効(NG)と判定できます。
有効、無効をもとに無効化行列Dを作成します。
DCを計算するとパターン行列で無効な行を0にしたC'が計算されます。
C'において行合計行列QをかけてC'Qを計算すると、C'の各行での合計が計算されます。このC'Qの総和を数字(=3)で割った値が有効パターンの数となります。
列合計行列PとC'をかけてPC'において0の要素は白マス「・」、有効パターン数と一致する要素は黒マス「●」、それ以外は未確定マス「○」となります。

以上で1つの数字におけるパターン行列に新しい追加解を適応して新しい解を出力できます。

追加解がほかの数字に該当する場合は次のようになります。

図3:追加解答(黒マスが追加、該当数字が1つ) その2


次に追加解が白マス「・」の場合も考えます。
先ほどの演算と同様に計算することができます。

図4:追加解答(白マスが追加、該当数字が1つ) その1


次は数字の解答範囲(開始位置、終了位置)がずれる場合の演算です。
図5では数字のパターン行列をもとに開始位置、終了位置を計算しています。

図5:パターン行列から開始位置、終了位置を計算 その1

連番基本行列B、逆連番基本行列B'を用意します。
終了位置はBC'Qの最大値を数字で割った値で求まります。
開始位置はB’C'Qの最大値と解答パターン個数などを利用して求まります。

なお、開始位置はBC'Qの最小値で求まりそうな気もしますが、これは演算上必ず最小値は0になってしまうので一工夫しています。

図5ではわかりにくいと思いますので、より大きなパターン行列での計算例は図6のようになります。

図6:パターン行列から開始位置、終了位置を計算 その2


ここで出てきた行合計行列Q、列合計行列P、連番基本行列B、連番逆基本行列B'はプログラムの中では最初に用意しておいて、大きさを指定して必要な時に呼び出すことで計算を高速化することができます。
またmax,sumなどはVBAでの標準の関数を利用することができます。


イラストロジックの解答アルゴリズムの紹介は以上です。
実際に開発したプログラムは技術流出の懸念から公開は致しません。
興味のある方は直接ご連絡ください。
Twitter


あと、詳しく調べていませんが今回紹介したアルゴリズムはどこにも転がっていなかったので、あわよくばまだ誰も発表していないものかもしれません。
自分で論文で出すのはめんどくさいので、共著で論文出したいって人もまた連絡ください。

 

 

PR

ExcelVBAでのツール開発を承っております。(対応実績350件以上 2023.8月)

お気軽にご相談ください。

イラストロジックの自動解答アルゴリズム 第1章

ペンシルパズルの代表格であるイラストロジックの自動解答プログラムをVBAで構築したので、そのアルゴリズムの紹介。
イラストロジックは別名「NONOGRAM」「ピクロス」などと呼ばれているパズル。正式名称は「NONOGRAM」っぽいですが下記紹介ではイラストロジックと呼んでいきます。
パズルを解いていくと絵柄が浮かび上がってくるもので、自分で解いたときの達成感が魅力のパズルです。
今回はその達成感をぶち壊すことを目的にプログラミングで自動的に解答する方法を解説いたします。

実際にVBAで構築したプログラムをもとにパズルを解答している動画です。








今回解説するアルゴリズムは1ラインだけを解答する場合の処理になります。
処理は「初回解答」と「追加回答」の2つに分かれます。

初回解答
初回解答は最初の空白の状態での解を探索する処理です。
例えば10マスで数字が3,2,1の場合は次のようなアルゴリズムになります。

図1:初回解答

1マスにおける解答パターンは
「○」→未確定
「・」→白マス
「●」→黒マス
の3種類とします。

数字の3,2,1においてそれぞれ考えられる解答パターンを列挙し、共通するものを解として確定させます。
このときの解答パターンの列挙を「パターン行列」と呼ぶことにします。以後何度も出てきます。
図1の場合は数字3において1つ黒マス「●」が確定します。

追加回答(黒マス)
次の処理が「追加回答」です。
追加回答では例えば横方向で1つでもマスの解が確定した場合、縦方向においてはその確定したマスが追加された解として次の解答で影響を受けてきます。
逆に縦方向で確定した解が、次に横方向で追加された解として次の解答で影響を受けてきます。

追加回答では大きく2種類が考えられます。
・黒マス「●」が追加された場合
・白マス「・」が追加された場合

まず黒マス「●」が追加された場合を考えます。


図2:追加回答(●が追加、該当する数字が1つのみ) その1

図2の場合だと新しく追加された解に該当するのが数字の3だけになります。
このときは数字の3においてのパターン行列に追加された解を適応すると、3つの解答パターンの内、有効(OK)なものと無効(NG)なものが判別出来ます。
このうち有効なものだけをパターン行列で残し、その有効なパターンにおいて共通するものを新しい確定解として出力します。

つぎに他の位置に黒マスが追加された場合を考えます。

図3:追加回答(●が追加、該当する数字が1つのみ) その2

次は数字の1に該当する追加の黒マスです。
この場合は数字の1における解答範囲の終了位置が1つずれるため、数字2,3の解答範囲の終了位置が1つずれて影響されてきます。
このため、各数字の解答範囲の終了位置は常に比較する必要が出てきます。もちろん開始位置も同様です。

つぎに該当する数字が複数ある場合です。

図4:追加回答(●が追加、該当する数字が2つ)

この場合は該当する数字においてそれぞれの数字が該当する場合を仮定して、出力される解の共通部分を新しい解とします。
共通解が無いときもあるので、この処理は面倒くさい割に当たり外れが多いです。

追加回答(白マス)
次に白マス「・」が追加回答にあった場合です。

図5:追加回答(・が追加、該当する数字が1つ) その1

こちらも黒マス同様に該当する数字でのパターン行列において適応して有効、無効を判断し、有効パターンでの共通事項を新しい解として出力します。

次の場合は数字の解答範囲の開始位置が変化することで、他の数字へ影響する例です。

図6:追加回答(・が追加、該当する数字が1つ) その2

つぎは該当する数字が複数ある場合です。

図7:追加回答(・が追加、該当する数字が2つ)

この場合は黒マスと異なり、該当する数字においては共通して白マスとして確定するためそれぞれのパターン行列に適応して新しい解を出力させます。

連続黒マスでの可能な数字判定
上記の処理以外に解答を確定させる処理として、連続する黒マスの範囲やその両端の外側1マスにおいて可能な数字の大きさが決まってくる例です。

図8:連続黒マス(●)での可能な数字の判定

図8の上側の図では黒マスが2つ続いていますが、この黒マスの範囲に該当する数字は2以上、その両端の外側1マスには3以上が該当するようになります。

黒マスが4つ続く場合は下側の図のようになり、黒鱒の範囲には数字の4以上、その両端の外側には5以上が該当します。

このようにして、黒マスの連続より特定の数字のパターン行列において白マスを確定することが出来たりします。

さらに、黒マスが飛び飛びの場合は次のようになります。

図9:黒マス(●)が飛び飛びの場合

この場合は、それぞれ連続する黒マスの周りの最大可能数字を足し合わせて、加算される部分だけを-1した数がそのマスでの可能な数字の最大になります。

1章まとめ
今回は1ラインでの解答での基本的なアルゴリズムの紹介でした。
次回はこのアルゴリズムをプログラムとして構築するために行列演算を適応する方法の解説です。

第2章へ

softex-celwear.hatenablog.com

 

PR

ExcelVBAでのツール開発を承っております。(対応実績350件以上 2023.8月)

お気軽にご相談ください。

VBA開発効率化術「ActiveXのコマンドボタンの設置と編集効率化」

 今回はVBA開発における効率化術を紹介します。

 

概要

 下記動画のようなテクニック

youtu.be

 

 ActiveXコントロールのコマンドボタンはフォームコントロールに比べて

  • 機能や書式が豊富
  • ユーザーに壊されにくい

というメリットがあるのですが、「開発」→「デザインモード」をOFFをしないと編集が出来なかったり、実行したいマクロをワークシートのモジュール上にClickイベント内でCallしないといけないなど、ちょいちょい作成や編集はめんどいです。

 動画の操作では

  1. クリップボードに格納したマクロ(プロシージャ名)を実行するコードを自動生成
  2. セル範囲と同じサイズのコマンドボタンを作成する
  3. コマンドボタンと名前とキャプションは実行させるマクロのプロシージャ名から自動設定
  4. コマンドボタンで[Enter]キーを押すと、コマンドボタンのClickイベントのコードを表示する
  5. コマンドボタンで[F2]キーを押すと、Application.InputBoxを利用してキャプションを編集できる
  6. コマンドボタンで[Shift+Delete]キーを押すと、コマンドボタンとワークシートモジュール内のコードを消去する

などの処理を行っています。

 

 1~3はコマンドボタン設置における自動化処理でリボンに登録しているマクロから実行します。

 4~6はコマンドボタンの編集における自動化処理で、1で自動生成したコマンドボタンのKeyDownイベントプロシージャを利用しています。

 

技術解説

 解説にはかなりのカロリーを消費しますので、実際に用いているコードを掲載します。

 これらの処理を実行できるようにするには下記ライブラリを参照する必要があります。

また、Excelのオプションから「VBAプロジェクトへのアクセス許可」も必要です。下記画像参照

 

PR

ExcelVBAでのツール開発を承っております。(対応実績350件以上 2023.8月)

お気軽にご相談ください。