ひとり情シスに役立つVBAコードのサンプル

ひとり情シスにも役立ちそうな Excel VBA のサンプルコード。

実行方法

1. 新規Excelファイルのコードウィンドウ（[Alt]+[F11]で起動）に貼り付けて保存。
2. 対象シートをアクティブにし、[Alt]+[F8] でマクロを呼び出して実行する。

ファイル参照パスを一括変更

ファイルサーバの更改などで、サーバのホスト名やドライブレターが変わってしまった場合に使う。
VLOOKUPなどの関数式で別ファイルにリンクしているときでも一括で変更できる。

例

• 変更前
  =VLOOKUP(B8, '\\ECSV31\人事総務部\[社員一覧.xls]Sheet1'!$A$3:$B$15, 2, FALSE)

• 変更後
  =VLOOKUP(B8, '\\ECGSV01\人事総務部\[社員一覧.xls]Sheet1'!$A$3:$B$15, 2, FALSE)

Dim r As Range, str1 As String, str2 As String

If ThisWorkbook Is ActiveWorkbook Then
    MsgBox "シートが選択されていません", vbCritical
    Exit Sub
End If
    
Application.ScreenUpdating = False  ' 画面更新を停止
Application.DisplayAlerts = False   ' 確認メッセージを表示しない
    
For Each r In ActiveSheet.UsedRange
    str1 = r.Formula
    str2 = Replace(str1, "\\ECSV31\", "\\ECGSV01\", , , vbTextCompare)  ' 置換
        
    If str1 <> str2 Then r.Formula = str2
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

無効な図形オブジェクトを一括削除

行や列の削除を繰り返していると、図形の幅や高さがゼロの無効なオブジェクトが残ってしまう場合がある。
Excelが重くなるだけなので一括で削除しよう。

Dim wb As Workbook, ws As Worksheet, shp As Shape

Set wb = ActiveWorkbook
If ThisWorkbook Is wb Then
    MsgBox "シートが選択されていません", vbCritical
    Exit Sub
End If

For Each ws In wb.Sheets
    ws.Select
    For Each shp In ws.Shapes
        If shp.Height = 0 And shp.Width = 0 Then shp.Delete
    Next
Next

セルの値が変わったら改ページ

機器管理資料などで、特定の列の値が変わったら改ページを挿入し、見易くするときに使う。

Dim ws As Worksheet, nRow As Long, nCol As Long, str As String

nRow = 28  ' チェック対象の開始行番号
nCol = 7   ' チェック対象の列番号

Set ws = ActiveSheet
Do Until ws.Cells(nRow, nCol) = ""
    If str <> ws.Cells(nRow, nCol) Then
        str = ws.Cells(nRow, nCol)
        ws.HPageBreaks.Add ws.Rows(nRow)
    End If
    nRow = nRow + 1
Loop