雑魚コンサルの個人的な備忘

エクセル大好き少年のメモ

【EXCEL】【VBA】MACでの使用を想定したときの留意事項

MACExcelのマクロを動かす想定がなくて、ツールが動かないあるあるのメモ。
気づいたら追記。

パスの区切り文字が違う

Windowsの場合は\(バックスラッシュ)、MACの場合は/
なので、マクロの中でファイルパスを設定するときなんかは、区切り文字を分けて記載しないといけない。
僕は区切り文字をハードコードしてたけど、一応変数に格納するようにはしている。

ちなみに、IF マック Then "/" Else "\"みたいなことはわざわざやらなくてもよくて、
Application.PathSeparatorで取得が可能。
毎回上記で取得してもいいし、Applicationとか書くのめんどくさいときは変数に格納してる。

Dir()でファイル一覧が取得できない

これはめんどくさい。Dir()の再帰処理で特定のフォルダ配下のファイル一覧だったり特定拡張子のファイル一覧だったりを取得することは非常に多いと思うけど、それができない。
Dir関数自体は使えるけど、最初のファイルしか読み込めない仕様なのか、再帰処理ができない。

調べてみるとMacScript関数なるものを使って、AppleScriptを動かさないといけないみたい。
MacScript(varAppScrpt)みたいな感じで。
つまり、WinとMacで完全に別々の処理をするようなコード作らないとダメ。

参照設定で追加しちゃダメ

最初から意識してれば特に困らないけど、一度作ってたものを後からMACに対応させるとかしようとするとコーディングし直さないといけなくなるのでそれなりにイライラする。
まあよく考えればそれも当然で、参照設定で追加するようなものって、WINDOWSフォルダに格納されているようなファイルが元なので、そりゃMACで動くはずがない。

f:id:himatuna:20200710082227p:plain
参照設定

また、同様にユーザーフォームのコントロールなんかも追加しちゃダメ。
ユーザーフォーム上で↓から選択する

f:id:himatuna:20200710082503p:plain
その他のコントロール(ユーザーフォーム)

↓これ

f:id:himatuna:20200710082617p:plain
コントロールの追加

僕はプログレスバーとかListViewくらいしかよく使うものってないのでそんなに困らない。
ListViewは他で代替する方法が思いつかず、結構めんどくさいコーディングをしてしまったなぁ。。。

ActiveXコントロールが使えない

まあこれは上記とほぼ同じなんだけど。
エクセルブックにマクロ起動用のボタンとか条件選択のチェックボックスを配置することはよくあるけれど、ActiveXコントロールMACでは使えないので、フォームコントロールを使う必要があり

f:id:himatuna:20200710072235p:plain
コントロール

VBAでコントロールの値を操作したりする場合はフォームコントロールが使えないとかなので、注意。
そういうツールはMACでは動かせないと

CreateObjectは使っちゃダメ

まあこれは参照設定追加できないので、当然だけど。
普段CreateObject("Scripting.FileSystemObject")と記載していると、そこまで意識ないかも。
楽だから結構使いたいけど、使わないように。

【PPT】【VBA】よく使う操作:図形の特定/編集、スライドのコピーとか

PPTのマクロ個人的な基本。正直情報が少なくて効率的な処理方法とか全然わからないので、体系的に解説してくれてるところが欲しい。

大体マクロで同時処理する時って、下記のようなパターンが個人的には多い。

  1. コメント等の図形を全体から削除する。
  2. PPT範囲外に置いたフォーマット類を削除する。
  3. 定型ページの同位置のボックスにテキストを埋めこむ

ので、ここらへんの処理ができるようないろいろ

スライドの移動/選択(スライドのLoopと、スライドのアクティブ化)

PPTをマクロで処理する時、スライドがアクティブになっていないと止まる処理が多い。※具体的にはどの処理がひっかかるのか知らない

なので、大体下記処理を入れる。

Sub loopSlide()
  Dim lastSlide As Long
  Dim i As Long

  '最後のスライド番号を取得
  lastSlide = ActivePresentation.Slides.Count  
  'Loop
  For i = 1 To lastSlide
    ActiveWindow.View.GotoSlide i

    '///処理

  Next i
End Sub

スライドを複製していく

テンプレを作って、それをコピーしていくことが多いので、そのやり方。

Sub duplicateSlide()
  Dim newSldsCount As Long

  'スライド1を、PPTの最後のページに追加する
  With ActivePresentation
    newSldsCount = .Slides.Count + 1
    .Slides(1).Duplicate.MoveTo newSldsCount
  End With
  '複製したスライドに追加処理をする場合、アクティベートが必要
  ActiveWindow.View.GotoSlide newSldsCount
  'まあまあ重いので、PPTが落ちないようにしとくのもいいかも
  DoEvents
End Sub

オートシェイプの特定(位置情報/図形種類)

僕の場合は基本的には図形を特定する時、場所や図形種類で判断することが多い。
(この場所にある図形をXX、この種類の図形をXX、という処理)
その場合は、一旦図形を選択した状態で下記マクロを実行しておいて位置情報や種類を特定。

Sub getShpProperty()
  With ActiveWindow.Selection
    '位置情報
    Debug.Print .ShapeRange.Left
    Debug.Print .ShapeRange.Width
    Debug.Print .ShapeRange.Top
    Debug.Print .ShapeRange.Height
    'AutoShapeの種類(番号とか覚えられないので、一々取得して確認)
    Debug.Print .ShapeRange.AutoShapeType
  End With
End Sub

スライド内のオートシェイプに対するLoop処理(一般)

スライド内のすべてのオートシェイプを処理する場合は、Loopさせる
ただし、ここでちょっと難しいのがグループ化された図形は処理が特殊であること
※For~EachのLoopの中で、グループ化された図形はグループ単位で処理される

なので、基本的には下記のように再帰処理させることが多い

'本処理
Sub loopShapes
  Dim shp As Shape
  
  For i = 1 To lastSlide
    For Each shp In ActivePresentation.Slides(i).Shapes
      Call recursiveMacro(shp)
    Next shp
  Next i
End Sub

'再帰実行する処理
Sub recursiveMacro(shp As Shape)
  Dim shpRe As Shape
  'グループかされてるかどうかの判定。TypeがmsoGroupだとグループ化されている。
  If shp.Type = msoGroup Then
    'GroupItems(グループ内の図形)それぞれに再帰実行
    For Each shpRe In shp.GroupItems
      Call recursiveMacro(shpRe As Shape)
    Next shpRe
  'グループ化されてない図形だったら本処理
  Else
    '///処理
  End If
End Sub

オートシェイプに対する処理:特定の図形をまとめてコピーする

テンプレを作って、それをコピーしていくことが多いので、そのやり方。

Sub copyShapes()
  Dim shp As Shape

  'スライド1にテンプレがあるとして、それをコピーしていくパターン
  For Each shp In ActivePresentation.Slides(1).Shapes
    '条件判定により、図形を順次選択。この場合は、スライド枠外左にある図形を選択
    '必要に応じて、上記のGroupへの再帰処理も組み込む
    If shp.Left < 0 Then
        shp.Select Replace:=msoFalse
    End If
  Next shp
  'コピー:何も選択されていない場合たぶんエラーになるので、回避
  If ActiveWindow.Selection.Type <> ppSelectionNone Then
    ActiveWindow.Selection.Copy
  End If

  '参考:ペースト
  ActivePresentation.Slides(1).Shapes.Paste
  '参考:ペースト
  ActiveWindow.Selection.Delete

End Sub

【EXCEL】【VBA】マクロ高速化の記述(超汎用)

超汎用で毎回使うのでコピペして、一時的なマクロ以外には全部仕込んでおく。

'<処理高速化ON>
'-------------------------------------------------
'描画停止、イベント停止、自動計算停止によりマクロ処理を高速化
'必ず実行後にOFFに
'-------------------------------------------------
Public Sub LightSettingOn()  
  Application.ScreenUpdating = False '描画停止
  Application.EnableEvents = False 'イベント停止
  Application.Calculation = xlCalculationManual '手動計算
End Sub
'<処理高速化OFF>
'-------------------------------------------------
'描画自動更新、イベント有効化、自動計算有効化によりエクセルの通常の挙動に
'-------------------------------------------------
Public Sub LightSettingOff()  
  Application.ScreenUpdating = True '描画再開
  Application.EnableEvents = True 'イベント再開
  Application.Calculation = xlCalculationAutomatic '自動計算
End Sub

【EXCEL】【VBA】F1ヘルプが邪魔なので出さない

正直、F1押してヘルプ出す人間ってこの世に1%未満だと思っていて、MSは設定であれを表示しないようにできるようにしたほうが良い。

escを押すときに間違ってF1押す人の数>>>>>>>F1押してヘルプ出す人の数

 

個人用マクロブックを開いて、下記記述追加。僕の場合は個人用マクロブックの中に「Setting」ってモジュール作ってそこに書き込む。

'<F1ヘルプ無効化>
Sub Auto_Open()
  Application.OnKey "{F1}", ""
End Sub

 

補足:Excelを開いた際の自動マクロ2つと、違い

プロシージャ名をWorkbook_OpenとAuto_Openにすると、Excelを開いた際に自動で走るマクロになる。
Workbook_OpenThisWorkbookに記述
Auto_Open標準モジュールに帰j通

優先度としてはWorkbook_Openが実行されたのち、Auto_Openが実行される流れ。
また、大事なのは、Workbook_Openは別のVBAから起動した場合でも実行されるが、Auto_Openの記述は実行されないこと。

なので、Workbookに特定の自動起動マクロ使うならWorkbook_Openを使うのが普通な気がする。

ただし、個人用マクロブックに記述する自動処理=汎用的に自動起動したいものについては、Workbook_Openにすると動かないので、Auto_Openに記述する。
あくまでも、Workbook_OpenはWorkbookに帰属するため。

ただこれも細かいところをよく忘れるので、基本的には、個人用マクロブックに書く場合はAuto_Open、エクセルファイルに書く場合はWorkbook_Openで記載するのが良い。
※もちろん、うまく記述すればAuto_Openで記述したマクロを別ブックのVBAから動かすことは可能

【EXCEL】【VBA】マクロ使うための個人的な初期設定

 

必ず必要となるけど、たまにしかやらないから毎回少し忘れる個人的なエクセルの必須初期設定。

 

個人用マクロブック(Personal.xls)を作る

「マクロの記録」で保存先を「個人用マクロ ブック」に

f:id:himatuna:20191206101054p:plain

「記録終了」して、そのままエクセルを閉じると保存できる。

 

VBA Editor:コマンドのツールバー表示

  1. コメント ブロック
  2. 非コメント ブロック

f:id:himatuna:20191206101507p:plain

 

VBA Editor:編集オプション

ツール>オプションより、下記実施。

  1. 「自動構文チェック」のチェック外す
  2. 「変数の宣言を強制する」のチェック外す
  3. 「タブ間隔」を4⇒2に
    ※これはどっちでもいいけど、個人的に2が好き

f:id:himatuna:20191206101958p:plain


VBA Editor:文字色等

ツール>オプションより、下記実施。

  1. 標準コードのフォント名:Meiryo UI(日本語)、フォントサイズ:9
    ※ただの好み。標準コード変えると、他の設定も全部変わる
  2. 他下記設定に。僕は白背景でずっと触ってると目がチカチカするので、全部背景は黒。
    文字色も暗めの色に設定。(白とか水とか黄みたいのは使わない)

   標準コード:濃緑、黒
   構文エラー:赤、黒
   キーワード:濃水、黒
   識別子:灰、黒
   コメント:濃紫、黒

f:id:himatuna:20191206105314p:plain

もうちょっと色選べればいいのに…とすごく思う瞬間。

いつまで経っても最適解がわからない。

コマンドランチャー

 

僕はマウスを使わないので、キーボード操作で可能な限り無駄ない操作をしたい。

そうなると、コマンドランチャーが必須になるんだけど歴代PCでも使っているランチャーがあっても、新しいPCを調達する度に何てソフトだっけ?って毎回探すことになる。

(3年に一度だし、覚えられない)

ランチャー:CraftLaunch

覚えよう。覚えた。

ランチャーってソフトウェア名を見る機会がほぼないから、すぐ忘れる。

必須設定:スタートアップ起動

スタートアップ起動にしないと意味がない。
スタートアップ用のフォルダに、ランチャーの起動ファイルのショートカットを置いておく。

パス:C:\ProgramData\Microsoft\Windows\Start Menu\Programs\StartUp

これはユーザ共通のスタートアップ。PCを自分しか使わないならいつもここでOK。

ほかの人が使う可能性ある場合、ユーザ別のフォルダに入るから共通パスはないので、シェルより起動。

  1. ファイル名を指定して実行:Windows」+「R」
  2. 右記いれてEnter、スタートアップフォルダに飛ぶ:shell:startup
    (共通フォルダに飛ぶ場合はshell:common startup

あ、それと稀に忘れることとして、プログラムがZipファイルで配布されてるから、Software用のフォルダとか、PrgrammFilesのフォルダに入れるとかしないと邪魔。
(ダウンロード直下のフォルダにプログラムおいても当然起動できるけど、整理したいときにすごい邪魔)

 

必須設定:起動コマンド追加、非アクティブ時の動作

起動したら、Config⇒ホットキー設定>ホットキー割当より、

「Ctrl」+「Space」

で起動コマンドを割り当てる。
これは普段使わないようなキーの組み合わせならなんでもいいんだけど、正直Ctrl+Spaceはたまに使いたい時があるから、変えたい。
Excelで行選択するとき)
ただ普段のキーボード上のポジションを崩さずに押しやすいコマンドが思い浮かばないから一旦このまま。

また、同様にConfig⇒非アクティブ時の動作>非表示より、ランチャー使ってないときは非表示にする。
これしないと結構邪魔。

 

やっとくと便利:ゴミ箱

エクスプローラー使ってるときはキーボードだけじゃなくてタッチパッドも使ってるので基本的にはどのフォルダにもアクセス簡単だけど、よく使うものだとゴミ箱だけたどり着けないので

f:id:himatuna:20191128164723p:plain

ファイル:explorer.exe
パラメタ:shell:RecycleBinFolder

もちろんほかにもshell:から起動できるものはいっぱいあるけど、それは欲しくなったときにググればOK

 

USキーボードの日本語全角半角/英数切り替え

 

 

基本:USキーボードの入力モード変更方法

USキーボードの場合、半角/全角がないので下記のいずれかで日本語/英数を切り替える。

「Alt」+「~」

「Shift」+「CapsLock」

僕の場合は、指の稼働範囲の問題で後者「Shift」+「CapsLock」しか使わない。

 

事象:かなモードとカナモードがいつの間にか切り替わってる

ショートカットキーの多用をしていると、というかそもそもUSキーボードで日本語入力していると)、よく誤打鍵が発生して勝手にカナモードとかになっている。

※余談だけどそもそもカナモードマジでいらない。全部F7とF8でいいでしょ…

これは、こう。

全角かな⇒全角カナ:「Alt」+「CapsLock」

全角カナ⇒全角かな:「Ctrl」+「CapsLock」

 

事象:日本語⇒英数にするときに全角入力モードになる

これ、いつの間にか治ってるんだけど結構ムカついてた。

どうやら、「Shift」+「CapsLock」は前回の「英数半角全角」を記憶して、前回英数を全角で使ったりしていると、自動で英数全角の変換するらしい。

つまり、こんな感じ。

  1. 日本語:すし⇒「Shift」+「CapsLock」
  2. 英数半:sushi⇒「Shift」+「CapsLock」
  3. 日本語:すし⇒F9⇒sushi⇒「Shift」+「CapsLock」
  4. 英数:sushi

 

これはかなり不便なので、下記のようにIMEの設定を変えると常に半角になってくれる。

「前回の変換結果に従う」は常に英数字を全角で打つようなイケてない資料を作るときだったりには有効だけど、基本いらない。

f:id:himatuna:20191128110825p:plain

全角半角の記憶削除

補足:「Alt」+「~」と「Shift」+「CapsLock」の違い

細かいことはちゃんと調べてないけど、僕は下記の認識でいる。

 

英数半角/全角入力:「Alt」+「~」

英数半角と、直前に入力していた全角入力モードを切り替える。

あんまないけど全角英数モードで入力していたら、英数半角/英数全角で切り替え。

全角日本語かなで入力してたら、全角かな/英数半角で切り替え。

全角日本語カナで入力してたら、全角カナ/英数半角で切り替え。

 

英数/全角かな:「Shift」+「CapsLock」

英数(全角半角問わない)と日本語かなで切り替える。

英数全角で入力してたら、全角かな/英数全角

英数半角で入力してたら、全角かな/英数半角

直前が日本語カナモードでも、英数から切り替えるときは常に「全角かな」になる。

 

とりまとめると

  1. 勝手に全角英数モードになるのがウザい場合は、IMEオプションで変更
    オートコレクトの機能を「常に半角」に
  2. カナ入力はうざいけど変更方法覚えておく
    全角かな⇒全角カナ:「Alt」+「CapsLock」
    全角カナ⇒全角かな:「Ctrl」+「CapsLock」  
  3. 半角全角切り替えの2つは地味に機能が違う(うざい)
    英数半角/全角入力:「Alt」+「~」
    英数/全角かな:「Shift」+「CapsLock」