マクロ(Excel VBA) を使うときは後の作業を楽にするために行番号と列番号を変数で指定する、Excel シートに空白の行・列を入れておく

シェアする

The following two tabs change content below.

松井 元(まつい はじめ)

静岡県三島市の松井会計事務所に勤務する理系税理士。 文理両方のスキルの融合を考えており、このブログは以下を中心に更新している。
●税金・会計に関すること
●IT(Excel、VBAなど)を使った業務効率化
●自分のこと(考え、私生活)

さらに詳細なプロフィールはこちら
松井会計事務所へのお仕事ご依頼・お問い合わせはこちら
税金・会計の無料メルマガはじめます

Excel VBA でのプログラミングを楽にするために工夫は欠かせません。

今日は、私が普段 VBA を使う前提で Excel シートを作るときに工夫していることを2つ紹介します。

●行番号と列番号を変数で指定する
●空白の行・列を入れておく

これらはシートの設計を変更した場合に VBA コードの変更点を極力少なくて済むようにするための工夫です。

レクタングル大 広告

1、行番号と列番号を変数で指定する

行番号と列番号は変数で指定しておいた方が良いケースがあります。

例えば、次の1年間の給料の一覧表があったとします。

月別の「総支給額」「社会保険料」「源泉所得税」「住民税」「差引支給額」の金額を表示しています。

image

F列の差引支給額は VBA によって計算するものとします。

*単なる引き算なので実際には VBA を使うまどもなく四則演算だけで計算できますが、今日は題材として VBA を使いますね。

列番号を数値で指定したコード

次のコードで差引支給額を計算できます。

実行するとちゃんと計算できていることが確認できますね。

image

さて、ここでB列に新たに「支給日」を入力するようにしたい場合、列を挿入しなければなりません。

image

このようにすると先のコードで「総支給額」「社会保険料」「源泉所得税」「住民税」を読み込む列が1列ずつずれてしまうので、コードを修正しなければなりません。

具体的には、次のように列番号を1つずつずらします。

———————————————-

Soushikyu = Cells(i + 1, 2) 

Soushikyu = Cells(i + 1, 3

———————————————-
Syaho = Cells(i + 1, 3)


Syaho = Cells(i + 1, 4)

———————————————-
Gensen = Cells(i + 1, 4)


Gensen = Cells(i + 1, 5)

———————————————-
Jumin = Cells(i + 1, 5)

Jumin = Cells(i + 1, 6)

———————————————-

ここで1つ言えることは、変更箇所が多くて修正が面倒ということです。

この例ならまだ良いですが、もっと膨大なデータを扱わなければならない場合は容易ではありません。

列番号を変数で指定したコード

そこで、先のコードを最初から次のように作っておきます。

最初のコードと違う点は、「総支給額」「社会保険料」「源泉所得税」「住民税」を読み込む際の列番号の指定を変数を使っているということです。

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
*リンク先は私が勤務する松井会計事務所の HP です。

◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆経理業務効率化コンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

セミナー開催要望がありましたら、以下のリンク先にある個別のフォームよりお願い致します。
◆セミナー開催要望

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録

S_Line = 2 の部分(10行目)で「総支給額」の列番号である2を変数「S_Line」に入力しておき、以後の各項目の読み込みの際の列番号の指定には変数「S_Line」を使っています。

最初のコードと異なる点は次の部分ですね。

———————————————-

Soushikyu = Cells(i + 1, 2) 

Soushikyu = Cells(i + 1, S_Line)

———————————————-
Syaho = Cells(i + 1, 3)


Syaho = Cells(i + 1, S_Line + 1)

———————————————-
Gensen = Cells(i + 1, 4)


Gensen = Cells(i + 1, S_Line + 1)

———————————————-
Jumin = Cells(i + 1, 5)

Jumin = Cells(i + 1, S_Line + 3)

———————————————-

何が良いかというと、変数を使って列番号を指定しているため先ほどの「支給日」のように新たに列を挿入しなければならなくなった場合でも変数さえ変更しておけば、列番号の数値を1つ1つ変更する必要が無いということです。

変数の変更は「S_Line = 2」を「S_Line = 3」にすれば良いだけですね。

この例に限らずデータ量が多いほど、行番号、列番号を変数でしていしておくメリットは大きくなります。

2、空白の行・列を入れておく

さて、もう1つ VBA を使う前提の Excel シートにおいて役立つこととして「空白の行・列を入れておく」という考え方があります。

先ほどのこのシート⇃

image

行・列ともに空白を表の外に挿入しておくと、後々表の変更をしたい場合に便利です。
image

例えば、「支給日」を表示したい場合、空白の行・列が無いシートだと新たに列を挿入しなければなりませんが、空白の行・列をいれておけばその一部を使うことで対応できます。

K列を「支給日」に変更して、「月」を余白だったJ列に移すことができます。

image

そうすると、「総支給額」「社会保険料」「源泉所得税」「住民税」の列番号は変わらないので VBA  コードに与える影響も無くなります。

(まぁ、列番号を変数で指定しておけばそもそも問題ありませんが)

3、まとめ

さて、VBA を使う前提で Excel シートを作るときに工夫していることを紹介しました。

これらの方法は日頃よく使います。

Excel で最初に作ったシートの中身を後で変更したくなるケースはたくさんあります。

VBA を使っている場合は、シートが変わるとコードも大幅に変更しなえればならなくなることがあるので、極力変更点を少なくするために工夫をすることは大切です。

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
*リンク先は私が勤務する松井会計事務所の HP です。

◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆経理業務効率化コンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

セミナー開催要望がありましたら、以下のリンク先にある個別のフォームよりお願い致します。
◆セミナー開催要望

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録
税理士試験まとめ、税法論文の書き方

————————————————

税理士試験についてのまとめはこちらです!

税法2科目免除大学院についてのまとめはこちらです!

kindle電子書籍を出版しました。

税理士試験の税法2科目免除のために大学院で税法論文(修士論文)を書いた経験を元に、大学院生としての目線から税法論文の書き方を体系化した書籍です↓

大学院での税法論文の書き方

本書籍の内容についてはこちらの記事をご確認下さい。

お問い合わせ・フォローボタン

———————————

よろしければフォロー下さい↓

Twitter フォローボタン:

Feedly フォローボタン:
follow us in feedly

Facebookページ:
*いいね!を押して頂くと更新情報が届きます。

レクタングル大 広告
レクタングル大 広告
関連コンテンツ



シェアする

フォローする