EXCEL VBAで、列の追加・削除に対応できるマクロの作成方法

Question

列の位置が変わらないことを前提に列の位置を固定化して
VBAコードを書いたマクロを作成すると、
シートの列が追加・削除された場合に、列の位置がズレることで、
マクロが正しく動作しなくなります。

https://tonari-it.com/excel-vba-enum-columns/
のように列挙体を使えば、コードの修正はかなり楽になりますが

マクロのコードに一切修正を加えなくても
自動的に列の追加や削除に対応できる方法はありますでしょうか？

Answer 1

回答を書いているうち解決済みになっちゃいましたが、せっかくなので投稿しておきます。

---

セル範囲をテーブル化するという方法を検討されてはどうでしょう。

質問のリンク先サイトでも、続きの連載でテーブル化のメリットを紹介しています。

エクセルVBAでテーブル操作～そのメリットと変換方法＆ListObjectの取得

以下もうまくまとめられています。

Excelテーブル化のススメとVBA ListObject操作の自分用覚書 - Qiita

上記のリンク先では、下記のようにインデックスで参照する方法を紹介してますが、

'1列目
ActiveSheet.ListObjects(1).ListRows(1).Range(1).Address
'3列目
ActiveSheet.ListObjects(1).ListRows(1).Range(3).Address

実は下記のようにテーブル名と列見出しで参照することも可能です。

'「氏名」列の1行目のデータ
ActiveSheet.ListObjects("テーブル名").ListColumns("氏名").Range(2).Address

ちょっと記述が長いという場合は、下記のような書き方もできます。

Range("テーブル1[名前]")(1).Address

注：ListColumnsの場合は見出し行も含むが、Range("テーブル1[名前]")の場合はデータ範囲のみのようです。

これらの参照法は、見出し行の列名で参照するので、列を追加したり削除して列位置が変更されてもコードを変更する必要はありません。

Comments

[ttyp03]

普段テーブル使わないので見落としてました。
これが一番スムーズな気がします。
ただテーブルだとレイアウトが限定されてしまうのがネックですかね。
それを言ったら私の案も一緒か。

[SYO_chan]

hatena19さん
ありがとうございます。
勉強になります。

Answer 2

連想配列を使えば可能かと思います。
こんな感じで。

Dim ic As Object
Dim ir As Range
Dim item As Range

' 1行目を取得しカラム位置を連想配列化
Set ir = Range(Cells(1, 1), Cells(1, Range("A1").End(xlToRight).Column))
Set ic = CreateObject("Scripting.Dictionary")
For Each item In ir
    ic.Add item.Text, item.Column
Next

' 名前でセルを操作
Cells(2, ic("品目")) = "大根"

.

Comments

[SYO_chan]

連想配列は全く思いつきませんでした。
発想力が素晴らしいと思います。
ありがとうございました。

Answer 3

一般論で言えば、管理シートを作成し、そこに各項目の列情報を記憶しておけば良いかと。
列の追加、削除時に、その管理シートをメンテナンスすれば、マクロの修正は不要になります。

Comments

[SYO_chan]

ありがとうございます。
なるほど。
早速、やってみます。

[ttyp03]

tatsu99さん
リンク先に書いてあることと同じことを言ってるようにしか見えないのですが。。。

[tatsu99]

https://tonari-it.com/excel-vba-enum-columns/の場合だと、
列の追加／削除でマクロの修正が入りますが、
管理シートにその情報を書いておけば、その管理シートの修正だけですみます。
従って、マクロの修正は不要になります。
修正時の手間暇は同じ程度になるかもしれませんが・・・・・
ここから先は想像ですが、質問者の方は、列の追加削除に伴い、マクロの修正依頼が自分のところに
くるのを避けたかったのではないでしょうか。
マクロを使う人がズブの素人の場合、マクロの修正はできませんが、管理シートにしておけば、
管理シートの修正くらいはできます。（多少、管理シートの使い方の説明は必要になりますが）
そのようにして、自分の手を煩わすことを避けたかったように思われます。

[ttyp03]

あ、ごめんなさい。
シートを使うのか。。。
でもまあ、なんか面倒なイメージしかないです。
管理シート＝対象の表と一緒のような。。。

[tatsu99]

連想配列の扱いに慣れているならttyp03さんの方法がベターかもしれませんね。
肝心なのは、質問者がマクロをメンテナンスしなくて済むことかと。

[ttyp03]

どれを採用するかは質問者様次第ですが、質問内容を咀嚼すると列位置がずれてもマクロ側の手直しをしなくて済む方法はないかと言うのを問われているのかと思いました。
質問ではマクロに限定していますが、管理シートを用意したところで、結局そちらに手を入れないといけないようでは質問の意図とは違うのかなと思いました。
でもtatsu99さんの回答が選ばれたということは私が深読みしすぎただけかもしれません。

[SYO_chan]

将来、作成者の自分がいなくなったときに、
マクロを知らない人でも可能な限りメンテナンスできるようにしたいと思い
質問させていただきました。
シートの編集だけで済むのであれば、マクロを知らなくても
編集のやり方だけを理解していれば、メンテナンスできると思いました。