VBA100本ノックチャレンジ:61~70本目
61本目以降は1本あたりの取り組みにかかる時間が伸びているため、Twitter上で進捗報告をすることにしました。ので、回答もそのリンクを貼ります。
61本目。
#VBA100本ノック 61本目
— エクセルの神髄 (@yamaoka_ss) January 6, 2021
A列の名称には「ふりがな」が正しく入っていません。
「マスタ」シートのA列には「ふりがな」が正しく設定されているので、ここから「ふりがな」を取得しA列に「ふりがな」を設定してください。
マスタなしは赤文字にしてください。(既定の文字色は自動)
※シートは任意 pic.twitter.com/lvQgXtIbOd
自分の回答はこちら。
VBA100本ノックの61本目
— Yuzunoki (@y_ibris) September 16, 2021
自動記録ではPhoneticオブジェクト(https://t.co/9MEmdY06EA)を見つけられなかったので、マスタの値をコピペする割と強引な処理になってしまった
Range.Phonetics(n).Textでふりがな全部取ってこれるのね pic.twitter.com/zGmi4ox2cM
Phoneticオブジェクト (Excel)
セルの指定されたふりがなテキスト文字列についての情報を表します。
<VB>
ActiveCell.Phonetics(1).Text = "フリガナ"
62本目。
#VBA100本ノック 62本目
— エクセルの神髄 (@yamaoka_ss) January 8, 2021
VLOOKUPは条件合致の先頭が取得されます。
この取得順番を指定できる関数を作成します。
ZLOOKUP(検索値,範囲,列番号,順番)
順番→0:先頭,-1:最後,>=1:その順番
※当該順がない場合は空白""を返す
※検索値がない場合は#N/Aを返す
※完全一致のみ対応、検索値は単一セル値限定 pic.twitter.com/gol2sp6ME3
自分の回答はこちら。
VBA100本ノックの62本目
— Yuzunoki (@y_ibris) September 16, 2021
これはやり方が色々ありすぎて難しいな…ってなった
公式回答見ると引数の型を指定しておけばエラー時は勝手に# /Valueが返るとのこと おかげで余計な引数チェック入れちゃったな pic.twitter.com/8rLp02brJw
試しに以下のユーザー関数を作って使ってみたところ、確かに引数の型と一致しなければ#Valueが返ってました。
63本目。
#VBA100本ノック 63本目
— エクセルの神髄 (@yamaoka_ss) January 9, 2021
ブック内には「yyyy年mm月」シートが複数あり全て同一フォーマットです。
(A1開始で空行なく連続している)
これらのシートを一つに統合したシートを作成してください。
ただし1行目は見出し行なので先頭に1回だけの出力にしてください。
※出力シートは先頭に挿入(名称任意)
自分の回答はこちら。
VBA100本ノック63本目
— Yuzunoki (@y_ibris) September 17, 2021
前にやらなかった?と思ったらやっぱりやっていた
LIKE演算子で正規表現っぽく使うやり方、慣れてみるととっても便利で先日実務でも活用できたのが嬉しかった pic.twitter.com/Gxy7y2Kno5
【VBA入門】Like演算子(ワイルドカード、エスケープ、否定)
?:任意の1文字
*:0文字以上の文字
#:0~9の半角数字
[charlist]:charlistに含まれる全角または半角の1文字
[!charlist]:charlistに含まれない全角または半角の1文字
64本目。
#VBA100本ノック 64本目
— エクセルの神髄 (@yamaoka_ss) January 11, 2021
「元表1」「元表2」の2シートのA1からの表範囲を「リンクされた図」として「まとめ」シートに貼り付けてください。
貼り付け位置
・「元表1」はA1:J20の範囲へ
・「元表2」はA21:J40の範囲へ
※範囲内の縦横位置は任意
※再実行を考慮し、前回貼り付け分は削除してください。 pic.twitter.com/xIVerRyQ7U
自分の回答はこちら。
VBA100本ノック64本目
— Yuzunoki (@y_ibris) September 17, 2021
画像の取り扱いやっぱり苦手だなー!マクロ記録で
Range.Copy : https://t.co/Jgo9njDX9T.Paste(link:=True)
は拾えてたんだけどそのまま使っていいかどうかわかんなくてCopyPictureの方を使ってしまった
ちなみにShape.Formulaでリンクの有無が分かるとのこと メモ pic.twitter.com/hiPAQH8GBE
図の扱いは鬼門。
Worksheet.Paste メソッド (Excel)
Link:貼り付けたデータと元のデータの間にリンクを設定するには、 True を指定します。 この引数を指定すると、引数 Destination は指定できません。 既定値は False です。
というかWorksheet.Picturesコレクションって使えるけど自動メンバ表示とかで出てこなくてあらかじめ知っておくかマクロ記録とかじゃないと目にしないやつでしょうか…?
Dim p As Picture
For Each p In ActiveSheet.Pictures
Debug.Print p.Name ; "["; p.Formula; "]"
Next
とかすると確かに図の名前と数式があればその式が表示されるんですよね。
ちなみに当然と言えば当然か、挿入された図形なんかは取得しないので、まさに「図として貼り付け」で貼り付けされた図のみを対象とする場合はぴったり。
65本目。
#VBA100本ノック 65本目
— エクセルの神髄 (@yamaoka_ss) January 12, 2021
固定長データの出力です。
「フォーマット」シートに従い、2行目以降をテキスト出力してください。
文字形態は、N:右詰め0埋め、C:左詰め半角スペース埋め。
項目間区切りなしで連続で出力
※シートは任意
※本題ではないので出力ファイル及び文字コードは任意 pic.twitter.com/RbxVvv9tHn
自分の回答はこちら。
VBA100本ノック65本目
— Yuzunoki (@y_ibris) September 18, 2021
1ページに収まらなくなってきた 公式回答と同じフォーマット→Dictionaryと、レコード連結はいったん配列格納してJOINするところ、私はレコード行そのまま持ってきて連結したのを返してるくらいか pic.twitter.com/vTBOMBNRPZ
JOINについては「VBA100本ノックチャレンジ:31~40本目」の31本目でもちょっと触れました。最近ちょっと使う機会があったので今なら活用できそう。
Join
Join(sourcearray[,delimiter])
引数sourcearrayには、要素を結合する配列を指定します。
引数delimiterは省略可能です。結合する要素を区切るセパレータを指定します。
66本目。
#VBA100本ノック 66本目
— エクセルの神髄 (@yamaoka_ss) January 13, 2021
ブック自身のあるフォルダ以下の全サブフォルダを検索し、自身と同一名称(拡張子含めて)のファイルを探してください。
同一名称のファイルが見つかったら、シートに出力してください。
・A列:フルパス
・B列:更新日時
・C列:ファイルサイズ
※シートは任意
自分の回答はこちら。
VBA100本ノック66本目
— Yuzunoki (@y_ibris) September 18, 2021
DOS窓が出るのが何故かちょっと抵抗あったのと更新日とサイズも取得するなら普通にFSOにしよ~と思っていたのでこうしたけど、Excelにパスから更新日とサイズ出せる関数あったんだ…メモ
FileDateTime関数 https://t.co/qNO0BuW4il
FileLen関数 https://t.co/I2tSDYIf4v pic.twitter.com/nMt2rxCHdv
where - Windowsコマンド虎の巻
WHERE [オプション] <ファイル名パターン>
/R パス:検索対象のディレクトリを指定する※サブディレクトリ内も検索対象に含まれます。
/Q:結果としてファイル名を表示せず、終了コードのみを返す(0:検索結果あり 1:検索結果なし 2:エラー)
/F:結果のファイル名をダブルクォーテーションで囲って表示する
/T:ファイルサイズ、ファイル更新日時も表示する
/?:コマンドのヘルプを表示する
Exec (WshShell オブジェクト)
コマンドシェルでアプリケーションを実行します。
Exec メソッドを使って実行したスクリプトのステータス情報やエラー情報だけでなく、 StdIn、StdOut、および StdErr チャンネルにもアクセスできます。
たとえば「c:\temp\066.xlsm」なら、コマンドプロンプトで
where /r "c:\temp" "066.xlsm"
とすれば「C:\temp」以下のサブフォルダまで全部検索して合致するファイルを探してくれます。
このコマンドをoWSH.Execで実行して出力される結果を1行ずつ読んで対象シートのA列に出力、ってやるんだと思います。
ファイルパスから更新日時やファイルサイズを出してくれる関数についてはリンク先ツイートのとおり。
67本目。
#VBA100本ノック 67本目
— エクセルの神髄 (@yamaoka_ss) January 15, 2021
ユーザーフォームのListBoxに、ComboBoxの値で「リスト」シートのA列を絞りこんだデータを表示します。
ListBoxにはB列~D列を列見出しを付けて表示してください。
適宜作業シートを使用
※オブジェクト名は任意
※コンボは都道府県が設定済としてイベントの作成です。 pic.twitter.com/lOgmKDixdB
自分の回答はこちら。
VBA100本ノック67本目
— Yuzunoki (@y_ibris) September 18, 2021
UserFormは使ったことあるけどリストボックスの項目行の設定は使ったことなかったのでググったら神髄さんのサイトが表示されてしまったという懺悔 答えそのものは一応見ていないので容赦願いたく…
あとRange.Address(External:=True)で外部参照形式になるの知らなかった 便利 pic.twitter.com/x97VgMzXFc
Range.Address プロパティ (Excel)
External:外部参照を返すには、 True を指定します。 ローカル参照を返すには、 False を指定します。 既定値は False です。
第12回.リストボックス(ListBox)の追加
.ColumnHeads = True
これは列見出しを表示する指定です。
ただし、このプロパティは、RowSourceを指定した場合にのみ有効となります。
68本目。
#VBA100本ノック 68本目
— エクセルの神髄 (@yamaoka_ss) January 16, 2021
フォームの「登録」ボタンをクリックしたら、全てのTextBoxの値をアクティブシートに出力した後にフォームを閉じてください。
シート出力位置
・データ最終行のすぐ下の行
・シートの1行目にあるコントロール名で探した列
・列が見つからない場合は、右端に追加してください pic.twitter.com/yna0zwPJAr
自分の回答はこちら。
VBA100本ノック68本目
— Yuzunoki (@y_ibris) September 20, 2021
本当はアクティブシート指定だけど20本単位のノックを1ブックにまとめてるので特定シート指定ご容赦いただきたく
それ以外は概ね解答と同じ
Form.ControlsはAccessでも使いました 30項目くらいあるといちいち記述してらんないので…(面倒くさがり)
そしてやはりTypeNameは便利 pic.twitter.com/lbqZW0M7Y9
TypeName関数は「VBA100本ノックチャレンジ:11~20本目」の13本目でもちょろっと触れてました。変数の型を文字列で返してくれる便利な関数。
VBA TypeName 関数
TypeName 関数は、指定した変数の型名を返します。
Variant や Object 型に設定されている型を調べたいときに使用します。
69本目。
#VBA100本ノック 69本目
— エクセルの神髄 (@yamaoka_ss) January 18, 2021
全シートを対象として、結合セルをダブルクリックしたらセル結合を解除して全てのセルに元の値を入れてください。
セル結合を解除した場合はダブルクリックによるセル編集状態にならないようにしてください。
ただし、結合セル以外の場合は通常の動作のままにしてください。
自分の回答はこちら。
VBA100本ノック69本目
— Yuzunoki (@y_ibris) September 21, 2021
書いたのに投稿していませんでしたわ
ダブルクリックの制御はしたことなかったけどイベントなのは分かったので記述はあっさり
一つ気づいたのはTargetに結合セルの範囲まるごと入ってくれること
左上セルだけかと思ってTarget.MergeArea.Cellsにしてエラー出たのでアレってなった pic.twitter.com/hP52g3C0bS
これはシンプルなので特筆することは特になし。
70本目。
#VBA100本ノック 70本目
— エクセルの神髄 (@yamaoka_ss) January 19, 2021
ブックが開かれたら、自動的にステータスバーに1秒ごとに時刻が表示されるようにしてください。
ただし、1分後には時刻表示を終了してください。
※セル入力中は、時刻は更新されなくて構いません。
自分の回答はこちら。
VBA100本ノック70本目
— Yuzunoki (@y_ibris) September 21, 2021
実行中にも動かせるやつ…DoEventsでいいの?と書いてみたらファイルが開ききらず、もう一つかろうじて覚えていたApplication.OnTimeで何とか動作。
私はDoLoopにしましたが回答では再度Application.OnTimeで自プロシージャを呼び出していて、ループよりそっちの方がいいのかな? pic.twitter.com/YpYcdfceH9
DoEventsがOpenイベント内で動作しなかった問題については、神髄さんご本人が検証してくださいました。
Office365の最近のアップデートで挙動が変わった模様。
VBA100本ノック 70本目:ステータスバーに1秒ごとに時刻が表示
追記
現象確認しました。
最近のupdateで挙動が変化したようです。
本来、添付画像のようにOnTimeで切り離した方が良いのですが、本解答作成時には問題なかったので先の掲載のとうりとしていました。
※記事には本件を追記します。
※現在でも365以外(2019,2013,2010で確認)では問題なく動いています。
■70本目を終えて。
内容は相変わらず難しいですが、Twitterで進捗を流すようにしてちょっとモチベーションが維持しやすくなった模様。
10月いっぱいで100本目まで終えられたら、自分のことめっちゃ褒めようと思います。