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

kingakuhyoujieyecatcha

エクセルVBAを使って領収書を作成する時に金額表示を工夫するとオシャレに表現することができます。

数字1文字ごとに書体を変えることもできます。
お店独自のオンリーワンの領収書を作ることもできます。

 

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

 

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

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

 

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

<<<前の記事を読み返す<<<

VBAでオリジナルな領収書の作成に、エクセルのデータを反映します。

 

領収書作成についての関連記事はこちらになります。

「領収書作成」の記事一覧を開く

 

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

 

金額を表示するスタイルについて考えます。

たとえば 13000円の金額を表示するのに「¥13000」や「13000円」や「1万3千円」や「¥13,000-」などなど、他にもいろんな表示方法があると思います。

 

この記事で行う金額表示のデザインについては、テンプレートに表示枠を設定しましたように、

「¥」・「1」・「3」・「,」・「0」・「0」・「0」 の7つの文字をそれぞれ、セルに1つずつ順番に表示するようにします。

kingakuhyouji001

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

 

今回はこの「¥13,000-」の形でVBAを組み上げたいと思います。

 

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

 

MEMO
領収書作成のエクセルVBA全コードを確認することが出来ます。

オリジナルな領収書作成のエクセルVBA モジュール記述の全コード集

 

まず、表示においては

金額の桁数を調べて、3桁毎に ” ( , )カンマ ” を付けます。

例えば、

13000 (文字数5個)であれば → 加工後は 13,000 (文字数6個)

1300000 (文字数7個)であれば → 加工後は 1,300,000 (文字数9個)

のように変化します。

 

領収金額の桁数を調べる

 

ここで使う変数

  • 領収金額の桁数・・・・anum1
  • 領収金額・・・・・・・aBill
  • 3桁ごとのブロックとしたときの数・・・P
  • 桁数(anum1)を3で割った時の商・・・X
  • 桁数(anum1)を3で割った時の余り・・Y

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

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

ここで使う演算子

  • 割り算の商を返します・・・・・・¥
  • わりざんの余りを返します・・・・Mod

 

MEMO

Len関数についてはこの記事を参考にしてください。

Len関数・LenB関数で文字列の文字数を知る方法。使用例で解説します

CStr関数についてはこの記事を参考にしてください。

Str関数とCStr関数 データ型変換方法の知っておくべき違いと使い方

 

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

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

anum1 = Len(CStr(aBill))

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

CStrとLenは金額データを値ではなく文字の並び(文字列型)として考えて、さらに文字数(桁数)を数えます。

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

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

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

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

 

  • Y=0であれば、ブロック数Pは、=X となります。
  • Yが0でなければブロック数Pを計算するのに、X個と端数になります、端数は1個として数えてブロック数Pは、=X+1

とします。

 

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

コード⑤

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

 

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

 

ここで使う変数

  • 領収金額の桁数・・・・anum1
  • 領収金額・・・・・・・aBill
  • カンマを付けた領収金額の桁数・・・・anum2
  • カンマを付けた領収金額・・・・・・・aBilln
  • 領収金額を3桁ごとにまとめブロックとし格納した配列変数・・・myBOX(5)
  • 3桁ごとのブロックとしたときの数・・・P
  • 桁数(anum1)を3で割った時の余り・・Y
  • ループ回数の変数・・・・・・・・・・・t

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

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

 

MEMO

Mid関数についてはこの記事を参考にしてください。

Mid関数・Right関数・Left関数は文字列操作の基本。使用例で解説します

Join関数についてはこの記事を参考にしてください。

Join関数は 配列変数で要素化した値を文字列連結する時に効く

1次元配列変数についてはこちらを参考にしてください。

1次元配列変数の利用法。静的配列と動的配列を最速に理解する

 

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

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

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

 

今回作成している領収書は、

領収書の金額表示の最大が15桁になりますので、ブロックの最大個数は5個になります。

 

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

条件式を使って

    • P = 1 (amun1)の時この場合は、3桁までの金額になりますので、カンマを付ける必要はありません。【aBilln = CStr(aBill)】
    • P ≠ 1 の時この場合は、さらに分割して
      • 余りYが0でなければ余りYの数(1か2)の分だけ先に”CStr(aBill)”から文字を切り取って”myBOX”に、格納後は文字数3個ずつ残りの”CStr(aBill)”から文字を切り取りそれぞれ”myBOX”に格納します。
      • 余りYが0であれば最初から、文字数3個ずつ”CStr(aBill)”から文字を切り取りそれぞれ”myBOX”に格納します。

この一連の作業をP回繰り返し(ループ)します。

 

そして、作ったそれぞれの収納庫を順番に”カンマ”加えて連結(変数”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のコードウインドに貼り付けてください。

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

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

kingakuhyouji002a

 

加工した領収金額データを適所に表示する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番目までを1つづつ順番に消していく(消去作業をループする)方法を使います。

 

金額の先頭には円マークを付けます。これは固定項目です。

Range(”E10”)= ”¥”

Range(”E32”)= ”¥”

 

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

コード⑦

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

 

1セルごとに金額の数字(文字)を入れます

 

ここで使う変数

  • カンマを付けた領収金額の桁数・・・・anum2
  • カンマを付けた領収金額・・・・・・・aBilln
  • 3桁ごとのブロックとしたときの数・・・P
  • カンマを付けた領収金額の最後の文字が入るセルの列位置・・・Z
  • 桁数(anum1)を3で割った時の余り・・Y
  • ループ回数の変数・・・・・・・・・・・i
  • ループ回数の変数・・・・・・・・・・・j

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

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

 

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

For i = 1 to anum2

Cells(10,5+i).Value = Mid(aBilln,i,1)

Next i

 

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

3桁ずつのブロックにしてmyBOX5つに収納しています。

 

先ほどの例で言うと

myBOX(0)は”1”  myBOX(1)は”300”  myBOX(2)は”000”  myBOX(3)は” ”  myBOX(4)は” ”  myBOX(5)は” ” の値になっています。

カンマ ”,” 付きで連結しましたので、この場合変数aBilln は 1,300,000,,,となっています。

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

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

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

 

固定の項目として、
金額表示の最後に「-」マークを入れます。

 

ここまでのVBAコードで言うと

コード⑧

コード
  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

 

このようになります。

kingakuhyouji003

 

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

 

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

でも、

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

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

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

 

表示テストをしてみてください。

文字の大きさ、右詰め中央左詰めも同時に調整をしてみてください。

 

次回は、最終回です。

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

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習で良質な参考書を7冊選ぶ。良書との出会いは大切です

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

>>>次の記事へ進む>>>

ユーザーフォームのVBAで領収書の印刷をコントロールする方法

 

業務効率UPのためにエクセルVBAを使いだして、始めのうちに知っておきたい内容を記事にしています。

「VBA最速理解」の記事一覧を開く

 

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

 

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