【VBA】仕事でよく使うVBAマクロ Best 3

IT業界の会社に入社して一番衝撃だったのは…

なんでもかんでも Excel で作る！


お客様に提示する資料から、規約をまとめた文書、設計書に至るまで
すべてExcelで作られていました。

IT業界は　Excel至上主義　なんです！！


そんなこんなで私が仕事でよく使うVBAマクロの Best3 を紹介いたします。

値のみ貼り付け

一番使うのが「値のみ貼り付け」マクロです。
通常、右クリック ［形式を選択して貼り付け］ → ［値］を選択 → ［OK］
と操作するのですが、とにかく面倒くさい。

下記が「値のみ貼り付け」マクロです。

'---値コピー
Private Sub PasteValues()
  On Error Resume Next                    ' --(1)
  Selection.PasteSpecial xlPasteValues    ' --(2)
End Sub

1. On Error Resume Next
    →『エラーなんて気にすんな！とにかく進め！』
   
   
2. Selection.PasteSpecial xlPasteValues
     Selection         現在選択しているモノ（オブジェクト） ※ちなみにSelect は選択する行為
     PasteSpecial   形式を選択して貼り付け
     xlPasteValues  形式「値」だけを貼り付け（xlPasteTypeの定義は後述）
   →『選択してるモノの形式を「値」にして貼り付け』

ページの先頭、最後に移動

Excelで詳細設計書を書いていると10ページとか超えるんですよ。
それをホイールやスクロールバーで行き来するのはアホくさい。
［Ctrl］ ＋ ［↑］or［↓］ も途中のセルに文字があると止まっちゃう。
これが蓄積されるとイライラしちゃうんです。

'---スクロール移動（先頭へ）
Private Sub ページ先頭に移動()
    ActiveSheet.Cells(1, 1).Select
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
End Sub 

ActiveWindows.ScrollRow / ScrollColumn はスクロールバーの位置を左上に寄せます。
ActiveSheet.Cells(1,1).Select だけだとカーソル位置だけ移動し、
肝心の画面はそのままになってしまいます。

'---スクロール移動（最後へ）
Private Sub ページ最後に移動()
    Dim Rw As Integer
    On Error Resume Next
    With ActiveSheet
        Rw = .HPageBreaks(.HPageBreaks.Count).Location.row
        .Cells(Rw, 1).Select
        ActiveWindow.ScrollRow = Rw
    End With
End Sub

ページ数が1枚のとき（改ページが取得できない）は
エラーになるので On Error Resume Next を使います。
.HPageBreaks.Count で改ページ数を取得できます。
.HPageBreaks(改頁数).Location.row で改ページ位置の行が取得できます。

シートの倍率一括変更

入社１年目で 『見る人にスクロールさせるな！』 と上司に叱られました。

Excelはスクロール位置まで保存してしまいます。
資料を書き終わってからそのまま保存して送ると、
受け取った人は資料を読むために、まず先頭までスクロールしなくてはなりません。

忙しい人はたったそれだけの作業で、見る気がなくなってしまうんです！
せっかく頑張って作った資料が、たった数秒の手間を惜しんだばかりに見てもらえないんです！

そんな時に作ったマクロです。
今では保存する前に必ず実行します。

'---シートの倍率一括変更
Private Sub シートの倍率一括変更()
    Dim ws As Worksheet
    Dim ZoomRate As Integer
    Dim defSheet As Worksheet
    Set defSheet = ActiveSheet
    
    On Error Resume Next
    ZoomRate = CInt(Application.InputBox("シートの倍率を入力してください", "倍率設定", Type:=2))
    If ZoomRate = 0 Then Exit Sub
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        ws.Activate
        ActiveSheet.Cells(1, 1).Select
        ActiveWindow.ScrollRow = 1
        ActiveWindow.ScrollColumn = 1
        ActiveWindow.Zoom = ZoomRate
    Next ws
    
    defSheet.Activate
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

全シートを InputBox に入力した倍率で統一し、最初のページにカーソルを置きます。
defSheet.Activate はマクロ実行後に表示シートがどっかいかないようにしています。

EnableEvents   イベント処理を無効化します。
ScreenUpdating マクロ実行中に画面がチラチラするのを防ぎます。
マクロ実行中だけ、この２つを無効化するだけで処理性能が向上します。
※FalseにしたあとTrueへの戻し忘れに注意してください。

おまけ

これがないとせっかく作ったマクロの意味がありません！
下記がショートカットの設定方法です。

Private Sub Workbook_Open()

    '--ショートカットに使用できるキー一覧
    ' Shift: +
    ' Ctrl : ^
    ' Alt  : %
    With Application
        .OnKey "+^v", "PasteValues"
        .OnKey "+^z", "シートの倍率一括変更"
        .OnKey "+^d", "ページ最後に移動"
        .OnKey "+^u", "ページ先頭に移動"
    End With
End Sub

PasteSpecial  で指定できる xlPasteTypeの定義

貼付形式	定義	値
すべて	xlPasteAll	-4104
値	xlPasteValues	-4163
書式	xlPasteFormats	-4122
数式	xlPasteFormulas	-4123
数値書式	xlPasteValuesAndNumberFormats	12
数式＋数値書式	xlPasteFormulasAndNumberFormats	11
列幅	xlPasteColumnWidths	8
罫線を除くすべて	xlPasteAllExceptBorders	7
コメント	xlPasteComments	-4144
入力規則	xlPasteValidation	6

以上