エクセルで「名前の定義」の参照範囲を一括で変更してみる

f:id:ykira:20190524182918p:plain

こんにちは。ykira(@rakipagu)です。

5月も後半。大分暑くなってきましたね。

久々のブログですが、今回はエクセルのVBAを久しぶりに使ったので

その時やったことを書いてみたいと思います。

あ、ちなみに弊社ではWindows用のエクセル(Office365 Business)を使っています。

名前の定義とは？

メニューの「数式」->「名前の管理」で定義できるやつです。

セルの範囲などに名前をつけて、セルにデータの入力規制でその名前を指定すると

ドロップダウンリストで入力ができるようになったりする便利な機能です。

www.becoolusers.com

名前の定義のパスが書き換えられる問題

現在のプロジェクトでは、定義用のエクセルと入力用のエクセルが別れて存在します。

つまり入力用エクセルで定義エクセルの範囲を指定して名前を定義しているのです。

そしてそのエクセルは、プロジェクトであるからして複数の人が更新します。

すると元々は正しかった定義用エクセルへのパスが、下記の様に何かの拍子に変わってしまうことがあるのです。

正しいパス  ・・・ =[定義.xlsm]定義!$A$10:$A$20
　　                                 ↓
間違ったパス ・・・ ='C:\data\excel\[定義.xlsm]定義'!$A$10:$A$20

パスが変わらないように慎重に作業するようにしているのですが

変わったら自動で修正するようにした方が楽だと思ったので、そのような機能をVBAで実装しました。

名前の定義を一括で変更

とりあえずソースです。

Const DefineWorkbookName As String = "定義.xlsm"

'------------------------------------------
'   名前の定義を修正
'------------------------------------------
Public Function FixNameList()
    Dim Nam As String
    Dim Ref() As String
    Dim i As Integer
    With ActiveWorkbook
        For i = 1 To .Names.Count
            Nam = .Names(i).Name
            Ref = Split(.Names(i).RefersToLocal, "'")
            If UBound(Ref) > 0 Then
                .Names.Add Nam, ("=[" & DefineWorkbookName & "]定義" & Ref(UBound(Ref)))
            End If
        Next i
    End With
End Function

※これは標準モジュールに書いています。

ループで名前の定義からひとつづつ取り出して、正しいパスに書き換えています。

間違ったパス(絶対パス)になった場合は「定義」と「!」の間に

必ずシングルコーテーションが入っていたので、それをパスが書き換えられているかの判断にしています。

ちなみにこれを、エクセルファイルを開いた時に実行されるようにしています。

これはWorkbook_Openというのを使うと簡単に実装できます。

'------------------------------------------------
'   エクセル起動時
'------------------------------------------------
Private Sub Workbook_Open()
    FixNameList
End Sub

短いソースですが、久しぶりのVBAでなかなか苦労しました。