領収書の金額をオシャレに表示できるVBAコード記述のコツ。

こんにちは、じゅんぱ店長（@junpa33）です。

この記事は前回に引き続いて、領収書テンプレートへのデータ表示の設定を行います。

今回は領収書の金額を工夫した表示なるようにします。

前回までの記事の確認をしていただくと分かり易くなると思います。

領収書の金額をオシャレに表示する

今回は、金額を表示するスタイル（表示方法）について考えます。

たとえば　１３０００円の金額を表示するのに「￥13000」や「13000円」や「１万３千円」や「￥１３，０００－」などなど、他にもいろんな表示方法があると思います。

今回採用する金額表示のデザインについては、テンプレートに表示枠を設定しましたように、

「￥」・「１」・「３」・「，」・「０」・「０」・「０」　の７つの文字をそれぞれ、セルに1つずつ順番に表示するようにします。

「おもてなし」を「お・も・て・な・し」と表示するするような感じです。

今回はこの「￥１３，０００－」の形でVBAを組み上げたいと思います。

領収金額データを加工してVBAコードで記述するコツ

まず、表示においては

金額の桁数を調べて、３桁毎に ” （ , ）カンマ ” を付けます。

例えば、

のように変化します。

領収金額の桁数を調べる

ここで使う関数・メソッド

• 指定した文字列の長さを返す関数・・・・・Len
• データ型を文字列型に変換する関数・・・・CStr

2021年6月23日 Len関数・LenB関数で文字列の文字数を調べる 2021年6月26日 Str関数とCStr関数 データ型変換の知るべき違いと使い方

最初に領収金額の数字の桁数を数えます。

その都度変わる桁数を、桁数を変数として「anum1」とします。文字数を数えるコードを記述すると、

anum1　=　Len（CStr（aBill））

「aBill」は発行データ入力シートの金額データの変数です。

ＣＳｔｒとＬｅｎは金額データを値ではなく文字の並び（文字列型）として考えて、さらに文字数（桁数）を数えます。

次に、３桁毎にカンマを入れることになるので、数えた文字数を３桁区切りのブロックとした数を調べます。

ブロック数を”Ｐ”とすると、

X　=　anum1　\　3　　　…　3で割った賞がX

Y　=　anum1　Mod　3　…　3で割った余りがy

ここまでのことをVBAコードにすると

コード⑤

    anum1 = Len(CStr(aBill))
    x = anum1 \ 3
    Y = anum1 Mod 3
    If Y <> 0 Then
        P = x + 1
    Else
        P = x
    End If

カンマの入った金額に変更する

ここで使う関数・メソッド

• 指定した文字列の長さを返す関数・・・・・Len
• データ型を文字列型に変換する関数・・・・CStr
• 指定した文字列で開始位置から指示数分だけ切り出す関数・・・・Mid
• 配列の要素を結合する関数・・・・Join

2021年6月23日 Len関数・LenB関数で文字列の文字数を調べる 2021年6月26日 Str関数とCStr関数 データ型変換の知るべき違いと使い方 2021年7月3日 Mid関数・Right関数・Left関数は文字列中の文字を切り出す 2021年7月10日 Join関数は 配列で要素化した値を文字列連結する時に効く

先ほどのブロックをブロック単位でＰ個、”カンマ”を付けて繋いでいけばカンマの入った金額になります。

カンマの入った金額を新しい変数として”aBilln”とします。

３桁ずつのブロックをそれぞれの変数に格納していきます。

格納庫を変数myBOX”とします。

条件式を使って

この一連の作業をＰ回繰り返し（ループ）します。

そして、作ったそれぞれの収納庫を順番に”カンマ”加えて連結（変数”aBilln”）していきます。

カンマを加えた数字の桁数を数え直します。

桁数を変数として”anum2”と名付けます。文字数を数えるコードを記述すると、
anum2　=　Len（aBilln）となります。

ここまでをVBAコードに直すと

コード⑥

Dim myBOX(5) As String

'領収金額が3桁以下の時の処理
　　If anum1 = 3 Or anum1 = 2 Or anum1 = 1 Then
　　　　aBilln = CStr(aBill)

'領収金額が4桁以上の時の処理
　　Else
　　　　For t = 0 To P - 1
'桁数の余りが存在する時
　　　　　　If Y <> 0 Then
'桁数の余り分を先頭桁から切り出す
　　　　　　　　If t = 0 And Y <> 0 Then
　　　　　　　　　　myBOX(0) = Mid(aBill, 1, Y)
'残り桁数を3桁ずつ最後まで切り出す
　　　　　　　　Else
　　　　　　　　　　myBOX(t) = Mid(aBill, Y + 3 * (t - 1) + 1, 3)
　　　　　　　　End If
'桁数の余りがない時
　　　　　　Else
　　　　　　　　If t = 0 Then
　　　　　　　　　　myBOX(0) = Mid(aBill, 1, 3)
　　　　　　　　Else
　　　　　　　　　　myBOX(t) = Mid(aBill, Y + 3 * t + 1, 3)
　　　　　　　　End If
　　　　　　End If
　　　　Next t

'3桁づつのブロックの末尾にカンマを付ける
　　　　aBilln = Join(myBox, ",")
　　End If

'カンマを付けた領収金額の桁数を調べる
　　anum2=Len(aBilln)

ここまでのコード⑤とコード⑥をVBEのコードウインドに貼り付けてください。

貼り付けの場所に注意してください。コード②を貼り付けた下に貼ってください。

こんな感じになったでしょうか。

加工した領収金額データを順番に表示するVBA

領収書シートでの表示に関するVBAコードの記述作業をしていきます。

最初に前回作成した時の残りデータをクリアーする

まず注意しなければならないのは、前に表示させた領収書のデータが、次に作成する領収書の時にもそのまま残っていることです。

次に表示させたいデータのために、前に表示したデータを忘れずに消去しないといけません。

領収金額の表示セルは”セルE10からセルT10”　と　”セルE32からセルT32”になります。

セルの値を消す方法はいろいろ考えれますが、今回は空文字列　””　を使って値を消します。

セルE10は”10行目の5列目”　セルT10は”10行目の20列目”ということですので、

前記事での説明では、range（”E10”）　Range（”T32”）と説明しましたが、

セルの位置指定の表示方法は他にも、

セルE10はCells(10,5)　セルT10はCells(10,20)と表示できます。

この表現方法を利用して、列の5番目から20番目までを１つづつ順番に消していく（消去作業をループする）方法を使います。

VBAコード的にはこのようになります。

コード⑦

    For s = 0 To 15
        Cells(10, s + 5).Value = ""
        Cells(32, s + 5).Value = ""
    Next s
    Range("E10") = "￥"
    Range("E32") = "￥"

１セルごとに金額の数字（文字）を入れます

ここで使う関数・メソッド

• 指定した文字列で開始位置から指示数分だけ切り出す関数・・・・Mid

2021年7月3日 Mid関数・Right関数・Left関数は文字列中の文字を切り出す

変数aBillnの数値を１桁（１個）づつ切り取ってセルに入れていきます。

　　For i = 1 To anum2
　　　　Cells(10, 5 + i).Value = Mid(aBilln, i, 1)
　　Next i

ここで注意しなければならないことがあります。

３桁ずつのブロックにしてmyBOX５つに収納しています。

先ほどの例で言うと

myBOX(０)は”１”　　myBOX(1)は”３００”　　myBOX(２)は”０００”　　myBOX(３)は”　”　　myBOX(４)は”　”　　myBOX(５)は”　”　の値になっています。

カンマ ”，” 付きで連結しましたので、この場合変数aBilln　は　１，３００，０００，，，となっています。

この場合、領収書表記するときに要らないカンマ ”，” が３つも付いています。

余分なカンマを消去するコードも付け加える必要があります。

　　If Y <> 0 Then
　　　　Z = Y + 3 * (P – 1) + 1 + P – 1
　　Else
　　　　Z = Y + 3 * (P – 1) + 1 + 3 + P – 1
　　End If

ここまでのＶＢＡコードで言うと

コード⑧

    For i = 1 To anum2
        Cells(10, 5 + i).Value = Mid(aBilln, i, 1)
        Cells(32, 5 + i).Value = Mid(aBilln, i, 1)
    Next i
    If Y <> 0 Then
        Z = Y + 3 * (P - 1) + 1 + P - 1
    Else
        Z = Y + 3 * (P - 1) + 1 + 3 + P - 1
    End If
    
    Cells(10, Z + 5).Value = "－"
    Cells(32, Z + 5).Value = "－"
    
    For j = Z + 6 To 20
        Cells(10, j).Value = ""
        Cells(32, j).Value = ""
    Next j

このようになります。

領収書の金額をおしゃれに表示するのまとめ

１つのセルに金額をまとめて表示するだけなら、考えるまでもなく全く簡単にできてしまいます。

でも、

今回のように領収書の金額表示を少し凝った表示にしようとすると、ちょっとした手間がかかってしまうのは事実です。

その代わりその分、他店とはひと味もふた味も違ったものになったりします。

皆さんも是非チャレンジしてみてはどうでしょうか。

次回は、最終回です。

コントロールパネルを作って、発行年月日の設定と印刷開始ボタンなどを配置していきます。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

2022年1月9日 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

Dstyle web