2013/03/02

【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 で改ページ位置の行が取得できます。



シートの倍率一括変更


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

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 マクロ実行中に画面がチラチラするのを防ぎます。
マクロ実行中だけ、この2つを無効化するだけで処理性能が向上します。
※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
数値書式xlPasteValuesAndNumberFormats12
数式+数値書式xlPasteFormulasAndNumberFormats11
列幅xlPasteColumnWidths8
罫線を除くすべてxlPasteAllExceptBorders7
コメントxlPasteComments-4144
入力規則xlPasteValidation6




以上

0 件のコメント :

コメントを投稿