VBA 変数の使い方について

質問

皆様、こんばんは。

いつもお世話になっているVBAの初心者です。

今回、いくつかのテキストボックスの値を使った複雑な計算を行うために、変数を使おうとしていますが、うまく動いていません。

書こうとしているプロシージャはこちらです。

Private Sub 発電推計1()
Dim My発電量1 As Integer
Dim My発電量2 As Integer
Dim My発電量3 As Integer
Range("N37").Formula = "= (" & My発電量1 & " + " & My発電量2 & " + " & My発電量3 & ") * 10 / 10000"
Select Case Range("O18")
Case 1
My発電量1 = Range("P18") * 15 * 0.1 / 0.0036
　 ...
End Select
Select Case Range("O19")
Case 1
My発電量2 = Range("P18") * 15 * 0.1 / 0.0036
...
End Select
Select Case Range("O20")
Case 1
My発電量3 = Range("P18") * 15 * 0.1 / 0.0036
...
End Select
End Sub

My発熱量の計算式に間違いがあるでしょうが、どう書けばいいかが分かりません。何方か詳しい方に教えていただければ非常に助かります。
どうぞよろしくお願いいたします。

回答No.2

Wendy02 · ベストアンサー

こんばんは。

Range("N37").Formula = "= (" & My発電量1 & " + " & My発電量2 & " + " & My発電量3 & ") * 10 / 10000"

My発電量1 ～3　は、最終的には、定数が入るのですから、そのような変数での数式が必要だとは思えません。具体的な内容は分かりませんが、それ自体が、マクロが必要かどうかもわかりません。

VBAというかプログラミングでは、同じことを繰り返す場合は、それをまとめようとするのが基本です。また、2バイト変数は、よほどの入門者以外は、ふつうは書きません。VBAは、String 型以外は、あまり2バイト文字に対して、寛容だとは思いません。

もう少し複雑にはなるだろうけれども、例えば、このようにすればどうでしょうか。

Sub e_GenerateCount1()
Dim Num(2) As Double
i = 0
For Each c In Range("O18:O20")
　If c.Value = 1 Then
　　 Num(i) = Range("P18").Value * 15 * 0.1 / 0.0036
　ElseIf c.Value = 2 Then
　　 Num(i) = Range("P19").Value * 15 * 0.1 / 0.0036
　ElseIf c.Value = 3 Then
　　 Num(i) = Range("P20").Value * 15 * 0.1 / 0.0036
　Else
　　 Num(i) = Range("P18").Value * 15 * 0.1 / 0.0036
　End If
　i = i + 1
Next
　Range("N37").FormulaLocal = "= (" & Num(0) & " + " & Num(1) & " + " & Num(2) & ") * 10 / 10000"
End Sub

補足

Wendy02様、こんにちは。
いつもお世話になっています。
ご説明をどうもありがとうございます。

＞具体的な内容は分かりませんが、それ自体が、マクロが必要かどうかもわかりません。

会計フォームを作ろうとしています。フォームの一部にユーザが入力したデータを使って残りの部分を計算するプロシージャを書いています。
ユーザフォームやコンボボックスも参照するので、VBAじゃないと難しいと思います。

＞また、2バイト変数は、よほどの入門者以外は、ふつうは書きません。

入門者です。いい勉強になりました。ありがとうございました。

また、Wendy02様が書いてくださったプロシージャに分からない部分がありますので、よろしければ教えていただけないでしょうか？

＞Dim Num(2) As Double
どうしてNumに２が入るんですか？

＞For Each c In Range("O18:O20")
ｃというのは何ですか？定義しなくてもいいですか？

それと、説明不足で申し訳ありませんが、O18, O19, O20のセルはそれぞれのコンボボックスと結ばれています。選択されたコンボボックスの値によって、計算式が違います。つまり、Range("N37")に、3つのコンボボックスの値を参照した3つの式の結果を入れたいですが、コンボボックスの値が変わるたびに式も変わるので、それをどう考慮すればいいかが分かりません。

色々と聞いてしまって申し訳ありませんが、どうぞよろしくお願いします。

回答No.4

Wendy02

#2の回答者です。
とりあえず、
>＞For Each c In Range("O18:O20")
>ｃというのは何ですか？定義しなくてもいいですか？
定義については、私のミスです。というか、そのコードでは、定義以前の問題で、何も見えていない状態ですから、私には、何をどうしてよいものか、手が付かないのが本音です。
c は、Cells の 頭文字の c のことです。

正直なところ、UserForm とか言われると、今の状態では、どこにどう入れるのか、UserForm なら、その中で処理したほうが早いような気がします。それを一旦、シートに入れるのでしょうか？

お礼

Wendy02様、MoguraSE様、動きました！！
おかげさまでちゃんと動きました。

式にあったRange("P18")ですが、これも説明不足で申し訳ありませんでしたが、実はRange("O18")に対してRange("P18"), Range("O19")に対してRange("P19"), Range("O20")に対してRange("P20")のように式が変わるので、困っていました。が、Offsetを使ってみたら問題なく動きました。

本当にありがとうございました！

Private Sub DBEGen()
Dim Num(2) As Double
i = 0
For Each c In Range("O18:O20")
If c.Value = 1 Then
Num(i) = c.Offset(0, 1) * 15 * 0.1 / 0.0036
　 ElseIf c.Value = 2 Then
Num(i) = c.Offset(0, 1) * 18.4 * 0.1 / 0.0036
ElseIf c.Value = 3 Then
Num(i) = c.Offset(0, 1) * 16.74 * 0.1 / 0.0036

など・・・

End If
i = i + 1
Next
Range("N37").FormulaLocal = "=(" & Num(0) & " + " & Num(1) & " + " & Num(2) & ") * 10 / 10000"
End Sub

回答No.3

MoguraSE

No.1の者です。

動きませんでしたか；失礼しました。

No.2の方への補足の回答を書いてみます。
＞どうしてNumに２が入るんですか？
My発電量は1～3がありますから、変数を3つ用意しなければなりません。
Dim Num(n) とは、Numという変数を配列として定義し、Num(0)～Num(n)番目までの変数を用意するものです。
つまりDim Num(2)とすれば、Num(0)、Num(1)、Num(2)の３つが用意できるので、それぞれをMy発電量1、My発電量2、My発電量3と対応させればよいわけです。

＞ｃというのは何ですか？定義しなくてもいいですか？
変数cは、Range("O18")からRange("O20")までをループで順に参照して、そのときのターゲットとなるセルのRange値が代入される変数です。
変数cの定義は、Range型です。定義するとしたら２行目で定義してもいいです。
VBAの場合、Option Explicitステートメントを追加しない限り、利用された時点で勝手に変数ができます（Variant型ですが）ので、無理に定義しなくてもいいですが、大きなプロジェクトやのちのちデバッグをしたりするときにきちんと定義してあると便利ですよ。
…そうするとiの定義もいりますが。

＞コンボボックス
難しいですね…。“結ばれている”というところが、あんまりピンときませんが、
計算式を3通りとも他のセル（画面外など）に記述しておき、コンボボックスによる選択では、その結果（参照先セル）だけを切り替えるような方法ではダメですか？

お邪魔しました。

お礼

MoguraSE様、Wendy02様、動きました！！
おかげさまでちゃんと動きました。

式にあったRange("P18")ですが、これも説明不足で申し訳ありませんでしたが、実はRange("O18")に対してRange("P18"), Range("O19")に対してRange("P19"), Range("O20")に対してRange("P20")のように式が変わるので、困っていました。が、Offsetを使ってみたら問題なく動きました。

本当にありがとうございました！

Private Sub DBEGen()
Dim Num(2) As Double
i = 0
For Each c In Range("O18:O20")
If c.Value = 1 Then
Num(i) = c.Offset(0, 1) * 15 * 0.1 / 0.0036
　 ElseIf c.Value = 2 Then
Num(i) = c.Offset(0, 1) * 18.4 * 0.1 / 0.0036
ElseIf c.Value = 3 Then
Num(i) = c.Offset(0, 1) * 16.74 * 0.1 / 0.0036

など・・・

End If
i = i + 1
Next
Range("N37").FormulaLocal = "=(" & Num(0) & " + " & Num(1) & " + " & Num(2) & ") * 10 / 10000"
End Sub

回答No.1

MoguraSE

こんにちは。

見たところ、Excelで動かすVBAスクリプトのようですね。
しかし、残念ながらVBA上の変数をセルの計算式に当てはめることはできません。
マクロでの計算が終了し、My発熱量という変数に答えが入ってから、答えの数値をセルに代入するのが正しいです。

つまり、こうなります。

Private Sub 発電推計1()
Dim My発電量1 As Integer
Dim My発電量2 As Integer
Dim My発電量3 As Integer

Select Case Range("O18")
Case 1
My発電量1 = Range("P18") * 15 * 0.1 / 0.0036
　 ...
End Select
Select Case Range("O19")
Case 1
My発電量2 = Range("P18") * 15 * 0.1 / 0.0036
...
End Select
Select Case Range("O20")
Case 1
My発電量3 = Range("P18") * 15 * 0.1 / 0.0036
...
End Select

Range("N37").Value = My発電量1 + My発電量2 + My発電量3 * 10 / 10000
End Sub

動作チェックはしていませんが…
お役に立てば光栄です。

補足

MoguraSE様、答えてくださってありがとうございます。
残念ながら、動きません＾＾；