個人用マクロブックの小さな幸せ
マイページにビジネスツールや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」で済むじゃんとかは言わないでね?(・3・)
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で知らせてもらえると誰かが解決してくれるかもしれません(他力本願
なが~くなりましたが、ここまで読んでいただきありがとうございました。
この記事が気に入ったらサポートをしてみませんか?