現金出納帳の「差引残高」欄の計算式がどうしても書けません。

単純な現金出納帳を作っていますが、「変数を使った「差引残高」欄」の計算式が書けません。様々な方法をやってみましたが、だめでした。
もっとも、71歳でVBAの勉強を始めて一月もたちません。分厚い書籍を二冊購入したのですが、なんともなりませんでした。
以下の通りに作りましたが、「差引残高欄の計算」の計算式がなんともなりません。
要するに、変数を使った計算式がわかりません。単純なのでしょうが･･･。
よろしくご教示ください。

Private Sub btnOk_Click()
Dim i As Long
With Sheets("30年度出納帳")

'変数に入力するセルの行数を入れる
   i = .Cells(.Rows.Count, 6).End(xlUp).Row + 1

'各項目の入力をシートに転記
   .Cells(i, 2) = Me.txt月.Text
   .Cells(i, 3) = Me.txt日.Text
   .Cells(i, 4) = Me.txt適用.Text

'支出区分による収入欄、支出欄への振り分け
   If Me.cmb収支区分.Value = "収入" Then
     .Cells(i, 5) = Me.txt金額.Text
     .Cells(i, 6) = " "
   Else
     .Cells(i, 5) = " "
   End If

'収入額欄及び支出額欄の空白の区分
   If Me.cmb収支区分.Value = "支出" Then
       .Cells(i, 6) = Me.txt金額.Text
       .Cells(i, 5) = " "
   Else
       .Cells(i, 6) = " "
   End If
   
'差引残高欄の計算
     ActiveCell.FormulaR1C1 = "=+SUM(R[-1]C[-2]:RC[-2])-　　SUM(R[-1]C[-1]:RC[-1])"
     Range("G6").Select
     
'収支勘定の選択によるアルファベット区分の選択
     .Cells(i, 8) = Me.lst収支勘定.List(Me.lst収支勘定.ListIndex, 1)

'入力フォームのラベルのフォントの詳細
     Me.txt月.Text = ""
     Me.txt日.Text = ""
     Me.txt適用.Text = ""
     Me.txt金額.Text = ""
         
End With

End Sub

行動規範の内容に同意します

回答5件

どのようにうまくいっていないのか
・エラーが発生する
・誤った値が出力される
などの記載がないので、現状をつかみかねております。
的を得た回答でなかったら申し訳ありません。

とりあえず
＞変数を使った計算式がわかりません
ということですので、行き詰まっているのは主に
ActiveCell.FormulaR1C1 = "=+SUM(R[-1]C[-2]:RC[-2])-　　SUM(R[-1]C[-1]:RC[-1])"
の部分かと思います。

とりあえずパッと見て気になった点を挙げさせていただきます。

気になった点①

まず、合計の計算式を埋め込むセルがActiveCellとなっています。

これは「現在アクティブなシート上で選択されているセル」を指すのですが、このコード以前にセルの指定(Range.Select)をしている箇所が見当たりません。
つまりマクロ実行した時点でアクティブなセルに式を埋め込んでしまうことになるのですが、大丈夫でしょうか？
⇒直後の処理でG6セルをアクティブにしていますが、ここに式を埋め込みたかったのでしょうか？

気になった点②

続いて式の中身についてですが、ここは記述された式の解説からさせていただきます。

RC形式での式指定になっていますので、基準セル（この場合ActiveCell）からの相対座標になります。
例えばActiveCellがG6セルなら、SUM(R[-1]C[-2]:RC[-2])は
G6セルからみて
始点：R[-1]C[-2] ⇒ -1行-2列 ⇒E5セル
終点：R[ 0]C[-2] ⇒ 同行-2列 ⇒E6セル
の範囲を指しますので、SUM(E5:E6)を指すことになります。

同様にSUM(R[-1]C[-1]:RC[-1])の方はSUM(F5:F6)を指すことになります。

収入・支出の合計範囲はそれぞれ意図するものでしょうか？

現在の処理でできていることとして、フォーム上で入力した月・日・金額などがOKボタンを押すことで最終行に追加されていくものと思います。
その結果に対し、「どの範囲で合計を取り」、「どこに合計を出力するのか」というところで式の内容や出力セルが変わってくると思います。

まずはそこら辺を追記いただけると、もう少し具体的なアドバイスができると思います。

ご確認よろしくお願いします。

投稿2018/12/11 08:16

jawa

総合スコア3013

nt-mukku

2018/12/11 13:13

ありがとうございます。マクロの超初心者の年寄りということをご理解ください。最初は、差引残高欄の計算式は　　　　Cells(i,7)=cells(i-1,7)+cells(i,5)-cells(i,6) でいいと思ったのですが、　　　「フォームの起動ができません。このブックでマクロが使用できないか、またはすべてのマクロが　　　　むこうに無効になっている可能性があります。」との表示が出て行き詰まってしまいました。また、マクロの記録を使ってプロシージャを書き込むと　　　　ActiveCell.FormulaR1C1 = "=+R[-1]C+RC[-2]-RC[-1]" 　　　Range("G7").Select となります。　Range("G7")　が　Cells(i,7)　になればいいと思うのですが、できません。苦肉の策で、SUMを使ってみたのですが、なんともならず、お手上げ状態であるといったところです。

jawa

2018/12/12 02:31 編集

＞マクロの超初心者の年寄りということをご理解ください。その御年で尚学習意欲を持って行動されていることに素直に感服しております。配慮が足らず、難しい表現となってしまっている部分があれば何なりとお聞きください。＞すべてのマクロが無効になっている可能性があります [こちらの質問](https://teratail.com/questions/58520)でも同様の現象が出て、マクロの作り直しでなぜか動くようになったというスレが立っていますね。基本的にそのエラーはコーディングが原因ではなく、マクロ実行に関するセキュリティエラーです。例えばマクロ付きブックを開いた時に「コンテンツの有効化」をしていなかったりすると表示されるようなものだと思います。 ※それ以外の状況でも発生してしまうバグか何かがExcelにはあるのかもしれません(^_^;; --- `Cells(i,7)=cells(i-1,7)+cells(i,5)-cells(i,6)` は、最終行のG列に「直前行G列(前回収支) + 最終行E列(今回収入) - 最終行F列(今回支出)」の結果を入れたいということですね。前述のとおりこのコード自体に問題はないと思いますので、現在のマクロが動作する環境であれば置き換えていただいても大丈夫だと思います。 ActiveCellも使用しなくなりますので、`Range("G7").Select`のようなセルの選択も、不要なら排除してしまって問題ありません。 --- 余談ですが、この式をRC形式で表現すると `Cells(i,7).FormulaR1C1 = "=R[-1]C + RC[-2] - RC[-1]"` となります。 SUMのような関数がなくRC直書きなので違和感を感じるかもしれませんが、要は"= G5 + E6 - F6"のように書いているのと同様です。相対座標で記述するので、行が変わっても式が変わらないのがRC形式の利点ですね。一応、「セルに式を入れる」か、「値を入れるか」というところが最大の違いとなります。 `Cells(i,7)=cells(i-1,7)+cells(i,5)-cells(i,6)` を省略せずに書くと `Cells(i,7).Value = cells(i-1,7).Value + cells(i,5).Value - cells(i,6).Value` というコードになり、その時点での計算結果の「値（数値）」をCells(i,7)セルに入れています。これに対して `Cells(i,7).FormulaR1C1 = "=R[-1]C + RC[-2] - RC[-1]"` はCells(i,7)セルに「式」として入れていますので、計算対象セルの値が書き換えられれば合計セルの値も変動します。ここら辺はマクロ実行後のシートの使い方によって都合の良い方を選択すればいいのですが、今回はフォームでの入力ミスをシート上で修正することを考えると「式」で入力しておいた方がよいかもしれません。長文になってしまいましたが、参考になれば幸いです。

jawa

2018/12/12 08:28

すみません。 1点問題があることに気が付きました。区分が「収入」のときの支出欄(F列)、および区分が「支出」のときの収入欄(E列)に空白(スペース)を入力していますが、セル同士の足し算・引き算をする際にこのスペースが邪魔をしてしまいます。 SUMでは範囲内にある数値以外の項目は無視してくれますが、セルを指定して足し算等の演算をする場合、数値として読み替えられず#VALUE#エラーとなってしまいます。回避策としては、・空白(" ")ではなく空文字列("")をセットするまたは・演算する前に数値変換する(数値以外を数値0に変換するN関数を利用する) ⇒`Cells(i,7).FormulaR1C1 = "=R[-1]C + N(RC[-2]) -N( RC[-1])"` といった対応が必要になりそうです。

seastar3

2018/12/12 09:42

まずワークシートのマクロが動作できる設定にする必要があるようです。保存形式を".xlsm"にして保存すること。また、Excelのファイルタブのオプションのセキュリテーセンターのマクロの設定で、「警告を表示してすべてのマクロを無効にする」か「すべてのマクロを有効にする」を選びます。ただし、「すべてのマクロを有効にする」はマクロウイルスも自動起動される恐れがありますから、開発時以外は選ばないほうが賢明です。また、ファイルを開くときにマクロを有効にするかどうかの確認が出ることがありますので、その確認でも有効にするのボタンを押す必要があります。

jawa

2018/12/13 00:27

確認が遅れましたが、「マクロの記録」で作成したマクロも実行できない状態でしょうか？それとも実行はできるけど意図した結果にならない状態でしょうか？

nt-mukku

2018/12/14 07:47

昨日からすべて作り直してみました。差引残高欄の計算ができない状況に変わりがありません。マクロの記録で作製したマクロでも実行できませんでした。プログラムではなく別の原因ではないかと思ってもいます。しばらく休んで、もう一台のパソコンで一からやり直してみます。よい結果をご報告できればと思います。皆様には貴重なお時間を割いていただきまして、感謝いたします。ありがとうございました。とりあえず今回の私の質問に関しましては、いったん終了とさせていただきます。

jawa

2018/12/14 08:34

そうですね。まずはマクロが動作する環境にする必要がありそうです。そういうことならseastar3さんのアドバイスが参考になりそうです。がんばってみてください。

nt-mukku

2018/12/28 10:13

皆様ありがとうございました。別のパソコンですべてをやり直してみたところ残高計算もうまくいきました。 cells(i,7)=cells(i-1,7)+cells(i,5)-cells(i,6) というプログラムですんなりと作動しました。何が悪かったのかわかりません。原因を探るのは私には無理と思われますので、とりあえず動いたということで解決といたします。これから次のステップがありますので、またお世話になると思います。よろしくお願いいたします。

行動規範の内容に同意します

簡単に2行目から11行目までに金額が収まっているとして、この残高を計算しG列に埋め込むボタンに用意するVBAプログラムは、

VBA
1Private Sub CommandButton1_Click()
2 Cells(2, 7) = Cells(2, 5) - Cells(2, 6)
3 For i = 3 To 11
4   Cells(i, 7) = Cells(i - 1, 7) + Cells(i, 5) - Cells(i, 6)
5 Next
6End Sub

のような形になります。
収支勘定の埋め込みは別の機会に。

投稿2018/12/10 12:57

編集2018/12/11 01:43

seastar3

総合スコア2285

nt-mukku

2018/12/11 12:49

ありがとうございます。参考にさせていただきます。

行動規範の内容に同意します

複数行の繰り返しや添字指定での単語の取り出しなど、EXCELVBAで多用される処理が含まれるようですが、お年を召してからの独学でのプログラミングの勉強とは頭が下がります。
どうしてもVBAにこだわらないのであれば、残高セルの式として、上の行の残高と自分の行の収入金額を足して自分の行の支出金額を引く式を埋め込み下に転写していけば、現金出納帳の残高は実現します。

G7セルの式:　=G6+E7-F7

これにより、例えば、G6に500が残っており、E7に200が入っていたとすれば、700が表示されます。
この式を下方向に複写していけば、順次残高が書き換わっていきます。
そしてこの機能をVBAでするのであれば、iを添字としたFOR NEXT 文で繰り返させます。FOR VBA でネット検索してみましょう。

投稿2018/12/10 12:37

seastar3

総合スコア2285

nt-mukku

2018/12/11 12:56

ありがとうございます。エクセルで作れば何の問題もないのですが、フォーム入力で現金出納帳と決算書を一発入力したいと思って始めたことですので、なんとかこの問題を解決し、次の決算書との連動を実現したいと思っています。既存のソフトを使うこともできるのですが、年寄りの楽しみとして取り組んでいるということもこあります。

行動規範の内容に同意します

自己解決

windows10が入っているパソコン（プロシージャが動かなかったパソコンはwindows8でした）でファイルから新しく作り直し、新たに次のようなプロシージャを作りました。入力フォームもちょっと変更しましたが、すんなり動きました。何がどうしたのか理解ができませんが、とりあえず解決しました。ありがとうございました。

Private Sub btnOk_Click()
'変数の定義　変数は５行目から入れる
Dim i As Long
With Sheets("金銭出納帳")
i = .Cells(.Rows.Count, 4).End(xlUp).Row + 1

 'テキストボックスの内容をセルに書き込む
        .Cells(i, 2) = Me.txt月.Text
        .Cells(i, 3) = Me.txt日.Text
        .Cells(i, 4) = Me.txt適用.Text
       
 '支出区分による収入欄、支出欄への振り分け
      If Me.opt収入.Value = True Then
        .Cells(i, 5) = Me.txt金額.Text
      Else
        .Cells(i, 5) = ""
      End If
      
      If Me.opt支出.Value = True Then
        .Cells(i, 6) = Me.txt金額.Text
      Else
        .Cells(i, 6) = ""
      End If
      
 '差引残高欄の計算
        .Cells(i, 7) = .Cells(i - 1, 7) + .Cells(i, 5) - .Cells(i, 6)
        
 '収支勘定の選択によるコード区分（アルファベット）の選択
      If Me.opt支出.Value = True Then
        .Cells(i, 8) = Me.lst支出勘定.List(Me.lst支出勘定.ListIndex, 1)
      Else
        .Cells(i, 8) = Me.lst収入勘定.List(Me.lst収入勘定.ListIndex, 1)
      End If
      
 '入力フォームの初期化
         Me.txt月 = ""
         Me.txt日 = ""
         Me.txt適用 = ""
         Me.txt金額 = ""
         Me.opt支出 = ""
         Me.opt収入 = ""
         
        
  End With

End Sub

投稿2018/12/28 10:29

nt-mukku

総合スコア10

ご希望の仕様の出納帳の行の追加と残高計算の雛形を作ってみました。フォームからの取得は取り入れていませんので、コードを分析して補充してください。
以下のようなシートで、

[ボタン1]を押せば、仮データ埋め込みと残高の自動計算を行うコードが、

VBA
1Sub ボタン1_Click()
2  開始行番号 = 3: ' 小見出しの次の一番目のデータを入力する行の番号を指定する。
3
4  記録月 = 12: ' 仮の記録月　本来はフォームから代入する。
5  記録日 = 11: ' 仮の記録日　本来はフォームから代入する。
6  入金金額 = 50: ' 仮の入金金額　本来はフォームから代入する。
7  出金金額 = 20: ' 仮の出金金額　本来はフォームから代入する。
8  
9  指定行 = InputBox("何行目の記録・計算ですか?", "1行処理サンプル", 3): ' このコードは入金金額と出金金額と残高金額を埋め込みたい行番号を指定するコード
10'  指定行 = Range("C65536").End(xlUp).Row + 1 :' このコードは日付の入った一番下の行の次の行番号を見つけるコード
11  
12  Cells(指定行, 1) = 指定行 - 開始行番号 + 1
13  Cells(指定行, 2) = 記録月
14  Cells(指定行, 3) = 記録日
15  Cells(指定行, 4) = 入金金額
16  Cells(指定行, 5) = 出金金額
17  入金累計 = WorksheetFunction.Sum(Range(Cells(開始行番号, 4), Cells(指定行, 4)))
18  出金累計 = WorksheetFunction.Sum(Range(Cells(開始行番号, 5), Cells(指定行, 5)))
19  残高金額 = 入金累計 - 出金累計
20  Cells(指定行, 6) = 残高金額
21End Sub