【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更

Alphabet

Counselling / Pixabay

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

まだまだ続きます請求データ一覧から請求書を自動で作成するシリーズです!

以下の前回記事までで、請求データの請求書ひな形へのデータの転記をデータの数に限らず処理する、というところまでできました。

【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
今回は行数をカウントする、行を隠す、などの「行を取り扱うテクニック」を紹介しています。いずれもデータや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、知っておいて損はありませんよ。

実はちょっとまだ未完成のところがありまして…。

今回は文字列を連結する、文字列の書式を変更するといったいくつかのテクニックを駆使しまして、請求書を自動で作成するVBA序盤の完成をみたいと思います。

なお、本記事は以下のYouTube動画と連動していますので、合わせて御覧くださいね。

では、よろしくお願いします!

スポンサーリンク

前回のおさらい:ご請求金額は結局いくら…?

「請求データ」シートから「請求書ひな形」にデータを転記するというプログラムです。

行を取り扱う技を活用して請求データの行数に限らず実行できるようになりました。

Sub 請求書作成()

wsTemplate.Rows("21:50").Hidden = False '隠れているかもしれない行を再表示しておく

Dim rowsData As Long '行数カウント用の変数
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '最後の行数を取得

Dim i As Long, j As Long, k As Long
k = 21
For i = 2 To rowsData
    For j = 1 To 3
        wsTemplate.Cells(k, j).Value = wsData.Cells(i, j).Value
    Next j
    k = k + 1
Next i

wsTemplate.Rows(21 + rowsData - 1 & ":50").Hidden = True 'データがない行を隠す

End Sub

エクセルVBAで請求金額の合計を求めたい

結局いくらなん!?

赤枠の箇所、合計金額が記載なしですね…ということで、まだ未完成でしたね…。てへ。

せっかくなのでVBAプログラムを使って自動で入力させるようにしていきましょう。

文字列を連結する

今回の例の場合ですと、A18のセルに「ご請求金額:9,288,324円」と出るようにすれば良いということになります。

計算式によってD54セルに合計金額が算出されますので、これを活用するのが良いですね。

イメージとしては「”ご請求金額:” + D54セルの値 + “円”」のように各要素を文字列として連結できればOKですね。

VBAで文字列の連結する場合は、アンパサンド記号(&)を使いまして

文字列A & 文字列B

とします。

従いまして、今回の場合は

wsTemplate.Range("A18").Value = "ご請求金額:" & wsTemplate.Range("D54").Value & " 円"

とすればOKです。

Rangeプロパティでセル範囲を指定する

「Range」というのが出てきましたが、これはRangeプロパティと言いまして、アドレスによりセル範囲を指定することができます。

この場合、同じように使えるCellsプロパティがあるのですが記述方法が異なります。

Cellsプロパティは以下ように行数、列数をカンマで区切って場所を指定

Worksheetオブジェクト.Cells(行数, 列数)

これに対して、Rangeプロパティはアルファベットと数字の組み合わせで指定したアドレスを用いて

Worksheetオブジェクト.Range(アドレス)

と表します。

アドレスはエクセルではお馴染みのA1などという表現で、ダブルクオーテーションで囲う必要があります。

例えばCells(1,1)とRange(“A1”)は同じということになります。

今回の場合は、ピンポイントでセルの位置を指定するのでRangeの記述を使ったほうがわかりやすいということですね。

Format関数で書式を設定する

では早速実行してみましょう!

エクセルVBAの請求金額、カンマ区切りがほしい

…って正しいは正しいのですが、ちょっとカッコ悪いですね。

何がかっこ悪いかというと、ビジネス文書としてはカンマ区切りが欲しいです。

このようなときは、特定の値を指定した書式に変換するFormat関数というものを使います。

Format(値, 書式)

書式についてですが、今回は”#,##0″というように指定してあげます。

何だこれ!?という感じですが、どこかで見たことありませんか?

エクセルで「特定のセルを右クリック→セルの書式設定→ユーザー定義」と操作した際の

エクセルのセルの書式設定の表示形式

これです。

「書式」は英語で”format”といいますもんね。

“#,##0″は「3桁ごとにカンマを入れつつ値がゼロの時は0と表示する」という書式です。短い記述ですが、なかなか深みがありますね。

他にも色々な書式がありますが、それについては「セルの書式設定」で研究をしてみてください。

ですから、該当の箇所は

wsTemplate.Range("A18").Value = "ご請求金額:" & Format(wsTemplate.Range("D54").Value, "#,##0") & " 円"

としてあげれば良いということになります。

実行をすると、以下のように請求金額にカンマを付与することができます。

エクセルVBAで請求金額にカンマを付与した

まとめ

以上

  • 文字列を連結する
  • Format関数で書式を設定

といったテクニックを駆使して請求データから請求書を自動で作成するというVBAプログラムを完成させることができました。

今回のプログラムはこちらです。

Sub 請求書作成()

wsTemplate.Rows("21:50").Hidden = False '隠れているかもしれない行を再表示

Dim rowsData As Long '行数カウント用の変数
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '最後の行数を取得

Dim i As Long, j As Long, k As Long
k = 21
For i = 2 To rowsData
    For j = 1 To 3
        wsTemplate.Cells(k, j).Value = wsData.Cells(i, j).Value
    Next j
    k = k + 1
Next i

wsTemplate.Rows(21 + rowsData - 1 & ":50").Hidden = True 'データがない行を隠す
wsTemplate.Range("A18").Value = "ご請求金額:" & Format(wsTemplate.Range("D54").Value, "#,##0") & " 円"

End Sub

文字列の処理はこれから何回もお世話になる基本テクニックですので、ぜひマスターして頂ければと思います。

さて、完成とは言いましたが、今回のプログラム、場合によってはもっと別の書き方のほうが良いかも知れません。

それについて、以降の記事で紹介してきたいと思います。

【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
エクセルVBAで請求データ一覧から請求書を自動で作成するシリーズ、まだまだ続きます。今回は、エクセルVBAでセル範囲を一気にまとめてコピーするCopyメソッドの使い方についてお伝えします。

どうぞお楽しみに!

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
  3. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  4. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  5. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  6. 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
  7. 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
  8. 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
  9. 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
  10. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  11. 【初心者向けエクセルVBA】For~Next文でセル範囲を一行ずつ移動させる
  12. 【初心者向けエクセルVBA】セル範囲のクリア~ClearContentsメソッドとClearメソッド
  13. 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
  14. 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
  15. 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
  16. 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
  17. 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
  18. 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
  19. 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
  20. 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
  21. 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
  22. 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る

コメント

  1. みっつ より:

    VBA初心者です。少しでもスキルアップしようと思い、こちらの記事にたどり着きました。

    だいたいは記事の通りに記述しているので、不要な行を隠すまではできるのですが、
    再計算ができません。
    wsInvoice.Calculateを記入しても、再計算されないのですが、なぜでしょうか?

    • みっつさん

      考えてみているのですが、いただいた情報だけではちょっと原因はわかりかねますね…

      • みっつ より:

        お忙しいところご返信ありがとうございます。
        今回、私が入力したVBAプログラムが以下の通りです。
        セルの位置を変えただけで、ほとんど貴社の講座のまねっこです。

        頑張ってVBAプログラムを作れるようになりたいと思っています。

        どうぞご指導のほどよろしくお願いいたします。

        Sub 請求書作成()

        Dim i As Long, j As Long ‘For~Nextカウント用整数型変数

        Dim wsData As Worksheet ‘「請求データ」シートを入れるオブジェクト変数
        Dim wsInvoice As Worksheet ‘「請求書ひな形」シートを入れるオブジェクト

        Set wsData = ThisWorkbook.Worksheets(“請求データ”)
        Set wsInvoice = ThisWorkbook.Worksheets(“請求書ひな形”)

        Dim rowsData As Long

        rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row ‘最後の行数を取得

        For i = 1 To rowsData – 1 ‘最終値の設定にrowsDataを使う

        For j = 1 To 3

        ‘請求データの2+i行目を請求書ひな形の11+i行目に転記
        wsInvoice.Cells(11 + (i – 1), j).Value = wsData.Cells(2 + (i – 1), j).Value

        Next j

        Next i

        wsInvoice.Rows(11 + rowsData – 1 & “:27”).Hidden = True ‘データがない行を隠す
        wsInvoice.Calculate ‘「請求書ひな形」シートを再計算する
        wsInvoice.Range(“A8”).Value = “ご請求金額:” & Format(wsInvoice.Range(“F32”).Value, “#,##0″) & ” 円”

        End Sub

        • みっつさん

          ごめんなさい、ソースがそのままであるなら原因はちょっとわかりませんね…

          • みっつ より:

            タカハシノリアキ様
            お忙しいところをコメントに目を通していただきありがとうございました。
            また新規VBAで作り直してみます。
            これからも、読みやすく、実践的なこちらの講座で学んでいきたいと思います。

          • みっつ より:

            タカハシ様

            たびたびのコメントで失礼いたします。
            新規のエクセルでVBAプログラムを講座の通りに記述したら
            再計算できました!
            コメント返信ありがとうございました!

          • みっつさん

            無事に解決できてよかったです!

  2. yafoo より:

    凄い細かいのですが、上記に記載されている

    wsTemplate.Range(“A18″).Value = “ご請求金額 :” & wsTemplate.Range(“D42″).Value & ” 円”

    をコピペしたら、構文エラーが出ました。

    よくよく見てみると、A18のクオーテーションマークが誤っているようです。

    ご参考までに・・・。

    • yafooさん

      コメントありがとうございます!
      確かに…おっしゃるとおりですね。

      修正させていただきました。
      引き続き、弊ブログをご活用いただければ嬉しく思います!

タイトルとURLをコピーしました