Excelのユーザーフォームを使ってマネーフォワードクラウド会計や弥生会計への仕訳インポートの元となるデータ作成を楽にできないか考察してみた件

2024年5月12日2024年7月9日

自分の仕事ではクラウド会計ソフトはマネーフォワードとfreeeを使っていて、主にマネーフォワードを使う機会が多いのですが、銀行取引やクレジット明細、レジシステム等の直接クラウド会計へデータ連携ができるものはいいのですが、

そうではないもの。具体的には現金の領収書などデータ連携ができないものについては基本的にはExcelにデータ入力を行いCSVデータを作成し、そのデータをクラウド会計に取り込む・インポートすることで対応しています。

このExcelに「データ入力」をする部分については面倒くささが多少なりともあるのでExcelのユーザーフォームを使ってExcelのデータ作成の負担感を軽くできないか考えてみました。

ユーザーフォームの作成の過程を備忘を兼ねて記事にしてみようと思います。

今回のやりたいこと・ゴール
Excelユーザーフォームを使って会計データを作成するまでの基本設定
Excelユーザーフォームを使って会計データを作成するまでの機能の設定
まとめ

今回のやりたいこと・ゴール

Excelのユーザーフォーム上で会計データを作成してそれをExcelのセルにどんどん転記していき、会計ソフトに取り込みやすい横一列のデータを作成することをひとまずのゴールとします。

横一列のデータができればその後は、データをExcel関数などをつかって弥生会計に取り込むデータ形式に加工したり、クラウド会計に取り込んだりできますので。

こんなイメージです。

Excelユーザーフォームを使って会計データを作成するまでの基本設定

おおまかにユーザーフォームを作成する

まずはユーザーフォームを大まかに作成します。今回は会計データに最低限必要な、

日付
勘定科目
金額
摘要

のみのシンプルなものにします。

部門や消費税区分などをつけると複雑になるので極力シンプルにします。

消費税の区分や部門などは後からExcelの機能を使って一括して作成することもできますので。

ユーザーフォームを作るにはExcelのマクロを作成する画面を表示して、挿入（I）からユーザーフォームを選択すると作成することができます↓

すると白紙の状態のユーザーフォームが画面に表示されるので、「ツールボックス」からユーザーフォームに設置したい機能をどんどん設置していきます。

個人的によく使うものとしては

テキストボックス（日付や勘定科目、摘要など文字や数字を入力するために必要な機能）
ラベル
コマンドボタン（ユーザーフォームを閉じたり、データ転記の実行など、ある動作を実行するためのボタン）
リストボックス（データをリストにして表示する機能）

くらいで、その他の機能はあまり使いません。ごく稀に「フレーム」を使うくらいでしょうか。

ということでこんな感じで設置してみました↓

設置場所の変更は後で自由に変更可能ですのであまり深く考えずに設置してOKです。

それぞれこんな感じで構成されています↓

これで土台ができたので、それぞれ設置したボタンやテキストリストなどにマクロのコードを追加して機能を追加していきます。

タブオーダーを決めてエンターキーで進むカーソルの順番を決める。

ユーザーフォームはエンターキーを押すと次の項目に移動する特徴があります。

この特徴を使って、入力する順番に合わせてカーソルが移動する場所を設定しておくと便利です。

具体的には上の画像でいうと、

最初に「年」の入力を行い、
次に「月」
「日」
「借方科目」
「貸方科目」
「金額」
「取引内容」
「取引先・支払先」を入力して、
「登録ボタン」を押してデータをセルに転記する。

こういった流れで入力をすると便利ですので、エンターキーを押すごとにその順番にカーソルが移動するように設定しておきます。

このエンターキーを押してカーソルが移動する場所を指定するには「タブオーダー」という設定を変更することになります。

この場所にて変更可能です↓

テキストボックスに入力する際に「ひらがな」や「英数字」に初期設定しておく

ユーザーフォームに設置したテキストボックスに「日付」や「勘定科目」や「取引先」や「取引内容」を入力して、最終的にはそのデータをセルに転記していくのですが

テキストボックスの種類によっては「ひらがな」でデータを入力するものや「半角英数字」でデータを入力するものがあります。

具体的には「日付」や「金額」のテキストボックスについては「半角英数字」で入力をする方が好ましく、逆に「勘定科目」や「取引先・取引内容」については「ひらがな」で入力することが多いと思います。

データの入力時に日付の時は「半角」で入力して、勘定科目を入力する際には毎回「半角／全角キー（Windowsキーボードの左上にあるヤツ）」を押して「ひらがな」に切り替えてデータ入力をするのは面倒くさいので

日付や金額のテキストボックスにカーソルが移動したら自動的に入力モードが「半角英数モード」に切り替わり、勘定科目のテキストボックスにカーソルが移動したら自動的に入力モードを「ひらがな」に変更しておくと毎回毎回入力変換キーを押す必要がなくなるので入力が楽になります。

こういった入力モードの切り替えはこの部分で変更可能です↓

Excelユーザーフォームを使って会計データを作成するまでの機能の設定

ここからはマクロのコードを作成して機能を追加していく作業をします。

こういった作業の際にはChatGPTを使うとコードの作成を効率的に行うことができそうなので積極的に使ってみます。

ユーザーフォームを「表示する」・「閉じる」コードを作成する

この程度のコードであればChatGPTを使わなくてもできる基本的なコードになります。（もちろんChatGPTを使ってもOK）

ユーザーフォームを画面に表示するには下記のコードを、

Sub ShowUserForm()
    ' UserForm1 という名前のユーザーフォームを表示
    UserForm1.Show
End Sub

ユーザーフォームを閉じる場合には下記のコードを記載します。

Private Sub CloseButton_Click()
    ' ユーザーフォームを閉じる
    Unload Me
End Sub

ユーザーフォームを表示するコードについてはExcelの画面上にコマンドボタンを設置してこのコードを登録します。

こうすることでボタンを押すことでユーザーフォームを画面上に表示することができるようになります。

ユーザーフォームを閉じるためのコードについてはユーザーフォーム上に設置した「閉じる」ボタンにこのコードを記載します。

そうすることでユーザーフォーム上の「閉じる」ボタンをクリックすることでユーザーフォームを閉じることができます。

勘定科目のテキストボックスに「１」や「２」などの特定の番号を入力したら「現金」や「売上」など特定の科目に変換するコードを作成する

会計ソフトには勘定科目に対して１や２などの「コード」が割り当てられていて、そのコードを入力することで勘定科目を呼び出すことができるものがあります。

「１」と入力すると「現金」という勘定科目を呼び出すことができる。といった具合です。

たとえば「水道光熱費」という勘定科目を入力しようと思うと５文字入力する必要がありますが、このやり方をすることで例えば「２」と入力することで一気に「水道光熱費」を入力することができるようになるのでスピードをあげることができます。

また、こうすることで入力の際の「揺れ」を防ぐこともできます。

「揺れ」とは例えば「交際費」と入力するのか「接待交際費」と入力するのかといった、内容は一緒だけと表示の方法が少し異なったりするようなやつで、

全角で入力するのか、それとも半角で入力するのかといったもの一種の「揺れ」になります。

データを入力する際に揺れが起こると内容は一緒なのにデータとしては別物扱いになるので極力避けるほうがよいです。（個人的に）

ということでこの機能をユーザーフォームに追加しようと思います。

ここで自分で考えるのではなく、どんなコードがいいかChatGPTに質問してみます。こういう作業はChatGPTが得意です。というか個人的にはChatGPTの数少ない使い所のような気がしています。

ifステートメントを使って判断するコードが作成されました。これでもいいのですが、なんとなく別のやり方も聞いてみます。

select caseステートメントを使って判断するコードが表示されました。

なんとなくselect caseのほうがわかりやすく感じるのでこちらをたたき台に使っていきます。

で、作ったコードが下のものになります↓

Private Sub meTextBoxkarikata_Change()
  
' ■■テキストボックス「借方科目」の辞書機能■■
 
 Dim inputValue As Variant
 Dim ws As Worksheet
      
    Set ws = ThisWorkbook.Worksheets("辞書")
     
    ' テキストボックスの入力値を取得
    inputValue = meTextBoxkarikata.Value
     
    ' 入力値に応じて変換する
    Select Case inputValue
     
        Case "１"
         
            meTextBoxkarikata.Value = ws.Range("b2").Value
         
        Case "２"
             
            meTextBoxkarikata.Value = ws.Range("b3").Value
         
        Case "３"
             
            meTextBoxkarikata.Value = ws.Range("b4").Value
             
        Case "４"
             
            meTextBoxkarikata.Value = ws.Range("b5").Value
             
        Case "５"
             
            meTextBoxkarikata.Value = ws.Range("b6").Value
          
        Case "６"
             
            meTextBoxkarikata.Value = ws.Range("b7").Value
             
        Case "７"
             
            meTextBoxkarikata.Value = ws.Range("b8").Value
         
        Case "８"
             
            meTextBoxkarikata.Value = ws.Range("b9").Value
               
        Case "９"
             
            meTextBoxkarikata.Value = ws.Range("b10").Value
             
        Case "１０"
             
            meTextBoxkarikata.Value = ws.Range("b11").Value
                
         ' 追加の条件があればここに追加
             
        Case Else
             
            ' その他の値の場合の処理
              
    End Select
     
End Sub

このコードは

借方科目の入力欄に全角で「１」と入力されたら「辞書」シートのB2セルのデータ（旅費交通費）を表示する
借方科目の入力欄に全角で「２」と入力されたら「辞書」シートのB3セルのデータ（備品・消耗品費）を表示する
・・・・
・・・・
借方科目の入力欄に全角で「１０」と入力されたら「辞書」シートのB11セルのデータを表示する

ように命令しているコードになります。

ちなみにExcelの「辞書」シートはこのようになっています。

この考え方で「貸方科目」や「取引内容」「取引先」についてもコードを少し訂正して追加することで同じような機能を追加することができます。

リストボックスに対応表を表示させるコードを作成する

上で作った機能ですが、このままでは「１」を押すとどんな科目に変換されるのか、「２」を押すとどんな科目に変換されるのか、「３」の場合は、「４」の場合は・・・・といった感じで覚えておかないと便利に使うことができません。

とはいえ全ての番号がどの科目に紐づいているのかを覚えておくのは面倒くさいので、

辞書のように「１」であれば「旅費交通費」と紐づいている。

「２」であれば「水道光熱費」と紐づいている。

といった感じでユーザーフォーム上に表示されているとそれを見ながら入力することができるので便利です。

会計ソフトにもよく搭載されている機能ですよね。

具体的にはユーザーフォームの「リストボックス」に番号と勘定科目の対応表を表示させることにします。

こんな感じで借方科目にカーソルがくると辞書欄に対応表が表示されます↓

で、貸方科目にカーソルがくると辞書欄に対応表が表示されます↓

取引内容にカーソルが来ると辞書欄に対応表が表示されます↓

こういった機能を実現したいのです。

ちなみに自分はこの動作のコードをどう書けばいいのかわかりません。

ネットで検索していくのも時間がかかりそうで面倒なのでどんなコードがいいのか、またChatGPTに質問してみます。

こういう作業はChatGPTが得意です。（2回目）

どうやら「Enterイベント」というものを使うといいことがわかりました。

もう少し質問してこちらのやりたいことを具体的にしていきます。

なんかいい感じにコードを作ってくれている感じがするのでこちらをたたき台に使っていきます。

で作ったコードが下のものになります↓

Private Sub meTextBoxkarikata_Enter()
 
' ■■テキストボックス借方勘定科目の辞書をリストボックスに表示■■
 
Dim ws As Worksheet
    Dim cell As Range
    Dim rowData As String
     
    ' ワークシートを指定
    Set ws = ThisWorkbook.Sheets("辞書") ' 辞書シートに記載されたものを表示する
     
    ' リストボックスをクリア
    ListBox1.Clear
     
    ' セルA2から最終行までのデータをリストボックスに追加
    For Each cell In ws.Range("A2:a" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        ' 2列のデータを結合して1つの文字列にする
        rowData = cell.Value & " : " & cell.Offset(0, 1).Value
        ' リストボックスに1行を追加
        ListBox1.AddItem rowData
    Next cell
 
End Sub

ボタンをクリックしたら入力したデータをExcelのセルに転記するコードを作成する

ここまでの作業で会計データとして必要なものはユーザーフォームにすべて入力できるようになっています。

最後にユーザーフォームに入力したデータをExcelのセルに転記していくためのコードを記載していきます。

ユーザーフォームの「コマンドボタン」をクリックするとこの動作をするように設定していきます。

ユーザーフォームに入力されたデータを転記する方法はいくつかやりかたがあると思うのですが今回は入力されたデータを一旦「配列」にいれた上でExcelのセルに転記したらいいのでは？と考えました。

なのでそのやり方をChatGPTに質問してみました。

コードの内容は半分くらいしか理解できていませんが、多分このやりかたでできるということなのであまり気にせずに次に進みます。

ここまでで配列myarryにユーザーフォームのデータが追加されたはずなので、そのデータをExcelに横一列に転記していくコードも必要になります。

これもまたChatGPTに質問してみます。

この2つのコードを組み合わせてコードを作るとやりたいことが実現できるはずです。

ということで作ったコードが下記のものになります。

Private Sub CommandButton1_Click()
 
 
' ■■入力内容を配列に格納してセルに転記する■■
    
Dim textBoxArray() As String
Dim ctrl As Control
Dim i As Integer
Dim startColumn As Integer
Dim ws As Worksheet
 
Set ws = ThisWorkbook.Worksheets("sheet1")
 
' テキストボックスの数を取得
Dim numOfTextBoxes As Integer
numOfTextBoxes = 0
 
' テキストボックスの数を数える
For Each ctrl In Me.Controls
 
    If TypeName(ctrl) = "TextBox" Then
     
        numOfTextBoxes = numOfTextBoxes + 1
         
    End If
     
Next ctrl
 
' 配列のサイズをテキストボックスの数に合わせる
 
ReDim textBoxArray(1 To numOfTextBoxes)
 
' 各テキストボックスの内容を配列に格納
 
i = 1
 
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "TextBox" Then
        textBoxArray(i) = ctrl.Text
        i = i + 1
    End If
Next ctrl
 
 
'セルに転記
 
ws.Activate
 
Dim lastrow As Long
 
lastrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
  
 startColumn = 0 ' 開始列（A列）を指定
For i = LBound(textBoxArray) To UBound(textBoxArray)
    ' データをセルに書き込む
    ThisWorkbook.ActiveSheet.Cells(lastrow, startColumn + i).Value = textBoxArray(i)
     
Next i
 
' ■■テキストボックスの入力内容を消去する■■
 
meTextBoxmonth.Text = ""
meTextBoxday.Text = ""
meTextBoxkarikata.Text = ""
meTextBoxkasikata.Text = ""
meTextBoxkingaku.Text = ""
meTextBoxnaiyou.Text = ""
meTextBoxsiharai.Text = ""
 
 
' ■■テキストボックスの「月」入力部分を選択する■■
 
meTextBoxmonth.SetFocus
     
End Sub

試しにコードを実行してみたところ上手くいったので多分大丈夫だと思います。

なお、セルに転記したあとは次のデータを入力することを想定して、入力したデータを消去して、再度カーソルを最初の場所に戻すようにコードを追加しています。

これで冒頭の動画のように、基本的にはエンターキーのみで入力が可能なユーザーフォームを作ることができました。

まとめ

もともと今回記事にしたユーザーフォームについては毎年確定申告の時期だけご依頼いただいている方のために作っていたExcelファイルがあったのですが、それを参考にして弥生会計やMFへインポートできるようにChatGPTを使って０から作り直してみたものになります。

今回ChatGPTに質問しながら作成することでネットや書籍で検索しながら作業するよりも時間を短縮して作成することができるような気がします。

しらんけど。

ただし、ある程度ChatGPTが提案してきたコードの意味がわかっていないと本当に自分がやりたい作業に微調整・カスタムすることができないのではないかとも思います。

また、今回のExcelのデータ作成についてはExcelに慣れている人であればわざわざユーザーフォームを使わなくても、テーブル機能をつかったり、Excelの関数を使うなど、基本的な機能で実現することは十分可能です。

ですが、普段Excelに触れない人からするとエンターキーで次の入力画面に進んだり、辞書などの入力補助があることでExcelのセルに直接入力するよりも直感的に入力をすることができ、入力する際の全角や半角などの表現の「揺れ」もある程度制御することができるのでExcelの操作に慣れていない人にとっては喜ばれるのではないかと思っています。

何か参考になることがあれば嬉しい限りです。

特定の項目を「固定」しておく機能を追加したい場合には下記の記事を参考にしてもらえれば↓

しくじるなよ、デューティ。