ちょっと生きづらさを抱えた人へ、気持ちを楽にするためのお手紙です。

【Excel VBA】範囲を可変で名前の定義を設定する

データの入力規則を設定するときに、リストを作って範囲を設定するのが毎回面倒くさいので、マクロ化。
アクティブセルのある行の先頭を定義名として、2行目以降を参照範囲に使います。

Option Explicit

'---------------------------------------------------------------------------------------------------
'定数
'---------------------------------------------------------------------------------------------------
'可変名前定義の数式
Private Const defineNameFormulaTemplate As String = "=OFFSET('{シート名}'!${列Index}2,0,0,COUNTA('{シート名}'!${列Index}:${列Index})-1,1)"

'---------------------------------------------------------------------------------------------------
'【処 理 名】可変名前定義作成
'【引    数】なし
'【返 却 値】なし
'【処理概要】アクティブセルの列に、名前の定義を設定する。1行目を定義する名前に使用し、2行目から
'          リストが始まることを前提とする。
'---------------------------------------------------------------------------------------------------
Public Sub 可変名前定義作成()
    Dim definName As String            '定義する名前
    Dim sheetName As String            'リストがあるワークシート名
    Dim columnIndex As String          '列インデックス
    Dim defineNameFormula As String    '可変名前定義の数式
        
    '----------------------------------------------------------------
    '数式の生成
    '----------------------------------------------------------------
    'リストがあるワークシート名
    sheetName = ActiveWorkbook.ActiveSheet.Name
    '列のインデックスを取得する
    columnIndex = Split(ActiveCell.Address, "$")(1)
    
    '数式を生成する
    defineNameFormula = Replace(defineNameFormulaTemplate, "{シート名}", sheetName)
    defineNameFormula = Replace(defineNameFormula, "{列Index}", columnIndex)
    
    '----------------------------------------------------------------
    '名前を定義する
    '----------------------------------------------------------------
    '定義する名前は、アクティブセルの行の1行目から取得する
    definName = Cells(1, ActiveCell.Column).Value
    '名前の定義を設定する
    ActiveWorkbook.Names.Add Name:=definName, RefersTo:=defineNameFormula
End Sub

6 COMMENTS

みすく参謀

実はQiita風にカスタマイズしたので、センスが良いのはQiitaなのである…。

返信する
みすく参謀

選ばれた曲はだいたい予想どおり(Journeyが入っていなかったことはハズレ)で、まつわるエピソードプラスアルファすべて知っていて、自分が意外と聖飢魔Ⅱのことを知っていることに驚いた(笑)

あえて避けていた、わたしが解散後の元・聖飢魔Ⅱメンバーの曲を聞かない理由、書いてみようかなあ。

返信する
nazna(

聴かない理由、ぜひ聞きたいです(^o^)

VBAとは、ちょっと外れるけど、今更ながらだけど、エクセルのリボンメニューいつまでたっても慣れない。というか、まぢやめてほしいよぉ(T_T)

あと、VBEつかいにくいw せめてアプリレベルで、カスタマイズなしに
自社のVisualStudioCodeくらいは外部エディタとして指定できるメニュー実装してほしいなー。

そんなことはマイクロソフトに言えってw? まあ、そうなんだよねー。

返信する
みすく参謀

リボンはショートカット覚えれば解決!(笑)

VBEの使いにくさは異常ですよね。
でも使う人が少ないのかもしれない。

一応、モジュールの読み込み自体をvbaで書いて、VS Codeで書いたファイルを読み込むってのはできなくはないけど、ねえ…

返信する

nazna( へ返信する コメントをキャンセル

メールアドレスが公開されることはありません。