個人用マクロブックの小さな幸せ

2021年3月7日 20:04

マイページにビジネスツールやExcelと書いているにもかかわらず、1記事目が買ったキーボードのレビューとなったので、2記事目はExcelVBAに関することにします（*･∀･）
しかし！個人用マクロブックの作成や説明については既に沢山の方が書かれているので、僕が普段使用しているちょっと便利なコードを紹介していきます！

1. 数式を値に変換

Sub 値に変換()
    On Error Resume Next
    Selection.Value = Selection.Value
End Sub

これは他ブックからvlookupなどで抽出した数式を値に変えるのに重宝しています。
vlookup抽出→列ごとフィル→選択されている箇所を値に変換
という流れで作業しています。ショートカットに割り当てておくとスムーズに出来て作業が捗ります。

2. 指定した列に空白があれば、その行を削除

Sub 空白行削除()
   Selection.SpecialCells(Type:=xlCellTypeBlanks).EntireRow.Delete
End Sub

この列に空白があれば行ごと削除したいなぁって時に有効です。
対象列を選択して実行すればOKです。

3. 瞬時にGoogleページ立ち上げ

Sub google()
   Dim gURL As String: gURL = "https://www.google.com/"
   CreateObject("wscript.shell").Run gURL
End Sub

ちょっとググりたいなぁと思った時にショートカットに登録しておくと、既定のブラウザでGoogleページ開いてくれます。
タスクバーにブラウザ入れてホームURLをGoogleにしてWin+〇(数字)でやれとかは受け付けません(#＾ω＾)

4. 行・列の幅をAutoFit

Sub 行列自動調整()
   With ActiveSheet.UsedRange
       .EntireRow.AutoFit
       .EntireColumn.AutoFit
   End With
End Sub

他人が作ったファイル開いたらだいたい使います。
文字が見切れてたり、無駄に広がってたりと見にくいので。
注意点としては、文字がめっちゃ入力されてると知らないファイルで実行してしまうと「もぉぉぉぉ！！」となります(　ﾟдﾟ)

5. 選択範囲のTrim

Sub 選択Trim()
   On Error Resume Next
   Dim myTgt As Range, myStr As String
   For Each myTgt In Selection
       myStr = myTgt.Value
       myTgt.Value = Trim(myStr)
   Next myTgt
   MsgBox "完了しました。"
End Sub

DB等から抽出したデータでありがちな前後にある空白を一括削除！
注意点としては関数が入ってるセルがある場合、値に変換されます。いつ終わったか判らないので一応MsgBoxで表示。

6. R1C1＜＞A1変換

Sub 列表示変換()
   With Application
       If .ReferenceStyle = xlA1 Then
           .ReferenceStyle = xlR1C1
       Else
           .ReferenceStyle = xlA1
       End If
   End With
End Sub

いちいち列番号数えなくてすむ！
イミディエイトで「?Range("AB1").Column」で済むじゃんとかは言わないでね？(・３・)

7. ブックを並べて表示

Sub 左右に表示()
   Dim cnt As Long: cnt = Workbooks.Count
   If cnt < 2 Then
       MsgBox "2つ以上のブックを開いてから実行して下さい。", vbCritical, "ERROR!!"
   Else
       Windows.Arrange xlArrangeStyleVertical
   End If
End Sub

標準機能でも出来ますが、表示タブ→整列→左右に～チェックと少しばかりめんどくさいのでこちらに(;＾ω＾)
僕は2ブックまでしか基本開かないので問題ないですが、5個とか開いてる状態で実行すると悲惨な状態に。。↓

ウルトラワイドモニター使ってる方は問題ないでしょうが、ゲーマーや株をやってる人以外ではあまり使ってる方いないのでは？
なので日頃から3つ以上ブックを開いている方はお気を付けて・・・
回避策はこちらで↓↓↓

Sub 左右に表示()
   Dim cnt As Long: cnt = Workbooks.Count
   If cnt = 2 Then
       Windows.Arrange xlArrangeStyleVertical
   Else
       MsgBox "開いているブックが2つの時しか使用できません。", vbCritical, "ERROR!!"
   End If
End Sub

8. 印刷時によく使用するページ設定

自分が作った資料って基本的にページ余白決まってませんか？決まってない？そうですか。僕は決まってるので楽にするVBA仕込んでます。
だって毎回手動でやるの面倒くさいんだもん！

Sub priSet()
   With ActiveSheet.PageSetup
       .PrintTitleRows = "$1:$1" 'タイトル行の設定
       .CenterFooter = "&P/&N" 'ページ数/総ページ数 →中央フッター
       .LeftMargin = Application.CentimetersToPoints(0.8) '左余白
       .RightMargin = Application.CentimetersToPoints(0.3) '右余白
       .TopMargin = Application.CentimetersToPoints(1.4) '上余白
       .BottomMargin = Application.CentimetersToPoints(0.9) '下余白
       .HeaderMargin = Application.CentimetersToPoints(0.8) 'ヘッダー余白
       .FooterMargin = Application.CentimetersToPoints(0.5) 'フッター余白
       .Zoom = False '拡大縮小率を指定しない
       .FitToPagesWide = 1 '横1ページに収める
       .FitToPagesTall = False '縦のページ数は設定しない
       .CenterHorizontally = True  '水平方向で中央寄せ
   End With
   MsgBox "設定完了。", vbInformation
End Sub

資料作る前に実行しとけばいつもの設定に。タイトル行は作る資料によりけりなので入れなくてもいいかも。

9. Numlockがオフならオンに！

テンキーを使わない人なら問題ないですが、数字入力でテンキーを使ってる人なら一度は経験ありませんか？
そう！Numlockがいつの間にかオフになってる！特に会社のPCだと誰かがちょっとPC貸して～と言って触った後にオフになってたり。。
なのでExcel開いた時にNumlockがオフならオンに自動で切り替えるようにしました。
まずは適当なモジュールにこちらを↓

Option Explicit

Const VK_NUMLOCK = &H90
Const VK_SCROLL = &H91
Const VK_CAPITAL = &H14
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2

#If VBA7 And Win64 Then

   Private Declare PtrSafe Sub keybd_event Lib "user32" _
      (ByVal bVk As Byte, ByVal bScan As Byte, _
       ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

     Private Declare PtrSafe Function GetKeyboardState Lib "user32" _
        (pbKeyState As Byte) As Long

   #Else
   Private Declare Sub keybd_event Lib "user32" _
      (ByVal bVk As Byte, ByVal bScan As Byte, _
       ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

     Private Declare Function GetKeyboardState Lib "user32" _
        (pbKeyState As Byte) As Long
#End If

Private Function NumLockOn()
   Dim NumLockState As Boolean
   Dim keys(0 To 255) As Byte
   GetKeyboardState keys(0)
   NumLockState = keys(VK_NUMLOCK)
      
   'オフであれば強制的にオンに切り替える。
   If NumLockState <> True Then
       keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
       keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0
   End If
End Function

次にThisWorkbookモジュールに↓

Sub Workbook_Open()
   Call NumLockOn   ' NumLockがオフの時にオンにする
End Sub

と記述すれば、次からExcel開いたらNumlockがオンになります。
これでNumlockがオフなってた時の「誰じゃボケェェエ」というストレスから解消されます(　´∀｀)bｸﾞｯ!

ショートカットキーの設定

上記で色々とコードを出しましたが、ショートカットキーはどう設定するの？と疑問になられた方のために！

Sub Workbook_Open()
  
   ' ショートカットキーの設定
   ' 〇〇の部分はプロシージャ名を入力
   Application.OnKey "+^z", "〇〇" ' [Shift]+[Ctrl]+[z]
   Application.OnKey "+^s", "〇〇〇〇" ' [Shift]+[Ctrl]+[s]
   Application.OnKey "+^a", "〇〇〇" ' [Shift]+[Ctrl]+[a]
   
End Sub

キーの部分[+^z]等がわからない方は[VBA Sendkeys]でググればすぐに出てくるので割愛。

最後に

とりあえず便利そうなやつ載せてみたけど、長くなってきたのでこの辺で終わります。
ちなみにここまで書いておいてアレですが、僕は個人用マクロブックは使っていません(・ω≦) ﾃﾍﾍﾟﾛ
アドインとして使用していますが、個人用マクロブックだと挙動が違う可能性もあるので不具合あれば直してくださいw
全然知識もなくとりあえずコピペして使ってみたけど動かん！って方はコメントかTwitterで知らせてもらえると誰かが解決してくれるかもしれません（他力本願
なが～くなりましたが、ここまで読んでいただきありがとうございました。