VBAで特定のセルに値を入力したい

質問

VBAについて教えてください.
(当方初心者です)

VBAでユーザー定義関数で
計算しているセルから相対的な位置に値を入力したいのです.

より具体的に言うと、例えば
B3のセルでユーザー定義関数を用いて計算します。
その関数で計算した結果である一定の条件を満たした時にだけ
その関数で計算しているセルから2行2列隣のセルに
特定の値を入力したい場合にはどうすればよいのでしょうか？

試しに簡単に書いてみたのが以下です。
-------------------------------------------
Sub test1()
ActiveCell.Offset(2, 2).Value = 100
End Sub

Function test2()
test1
End Function
-------------------------------------------

これだとtest1を単体で実行した場合は上手く動作するのですが
test2でtest1を呼び出した場合はエラーになります。
これをどう直せばtest2内でtest1の動作を実行できるのでしょうか？

ご教授のほどよろしくお願いいたします。

回答No.7

ham_kamo · ベストアンサー

No.2です。No.5さんへの補足を読みましたが、

> このとき、男女で食事に行った場合は、ユーザー定義関数内で条件分けされて
> 計算されていることから(男だけの場合と男女の場合とで)
> 関数内では男だけか男女かはわかっています。

ということなら、その部分だけ別関数にしてはいかがでしょうか。
たとえば IsMenOnly() というユーザ定義関数を元の関数のロジックを利用して作り、男だけの場合は True、男女なら False を返すようにして、

男の支払額を求める関数中の中では、
If IsMenOnly() Then
　男だけの場合の支払額を計算
Else
　男女の場合の男の支払額を計算
End If

としておき、女性の支払額を表示するセルでは、

If(IsMenOnly(),"",女性の支払額を求める関数)

というふうにすれば、うまくいきませんでしょうか。

お礼

新しい発想ですね。
さっそく試してみたいと思います。
回答ありがとうございました。

補足

つまりこういうことでしょうか？

・セルA1(男だけor男女を判断する関数が入っている)に
　「True」 or 「False」を返す関数を入れる
・セルA2(男の支払額を求める関数が入っている)は
　セルA1の結果を見て計算
・セルA3(女の支払額を求める関数が入ってるセル)も
　セルA1の結果を見て計算

それをするとセルが一つ余分に必要ですが
できそうな気がしますね.
試してみます.

回答No.10

Wendy02

こんにちは。

>条件によってアクティブにするセル(orセル数)が変わってしまうのはちょっと困るのです。

ご質問者さんのVBAの実力がどのぐらいのレベルかは本当のところは分かりませんが、使ってみてからの話にしてください。時々、質問者さんの中には、VBAのコードを見ただけで判断して、ダメ出しをする方がいます。

関数に、DDE やOLEの発想自体は、もう、Excelの一般のユーザーからは手の届かない話です。もうVBAのレベルではありません。だいたい、こういう時は、最初の組み立ての時点で方針を間違えていることが多いものです。

それと、今、仕事で抱えている問題を解決する場合は、具体的な例を出したほうがよいです。おそらく、発想を転換して、Worksheet_Changeイベントで解決するようなものではないか、という予想は立ちますが。

>条件によって関数の使い方(選択するセルの数など)が異なるってことですよね？

逆に、条件というのは、何でしょうか？

A1: 5000 (支払い金額)
A2: 3　 (男）
A3: 2　 (女）

B1～B2 の配列数式 表示では、{=GOTODUTCH(A1,A2,A3)} となります。

=GOTODUTCH(A1,A2,A3)

現在の配列数式で、片方に入れないと、両方とも同じ解になります。
片方を入れないと、片方しか出てこない設定も可能です。もちろん、ご自身が、こちらの作ったものに手を入れて、改編しても構いませんが。

お礼

回答ありがとうございました。

問題は一応解決できましたので
ここで質問を締め切らせていただきます。

Wendy02さんの回答は難しくてついていくのも大変でした。
まだまだ勉強が足りないということですね。
(2、3日勉強しただけですから当然ですが)

今後も何か躓くことがありましたら
ご助言いただけますようお願いします。

補足

>使ってみてからの話にしてください。時々、質問者さんの中には、VBAのコードを見ただけで判断して、ダメ出しをする方がいます。

失礼しました。
ただでさえ難しい内容で、さらに使うとなると厳しいなぁと思って
手抜きしてました。

>Worksheet_Changeイベントで解決するようなものではないか、という予想は立ちますが。

Worksheet_Changeイベントというのがどういうものかはわかりませんが
本日ham_kamoさんのアイデアで実現できました。
もっと上手い手が見つかるとは思いますが、同じところで多くの時間を
浪費するのもどうかと思いましたので。
Wendy02さんの助言は今後の私のスキルアップのために
活用しようと思います。

>逆に、条件というのは、何でしょうか？

条件というのは、「男だけ」の場合と「男女」の場合でという意味で
用いました。
ただ、配列を使えばなんとなくですが上手くできる気がします。
時間がないので、すぐにどうこうする予定はありませんが。

いずれにしても、まずは様々な数値を入れてみて
実際にどのような結果になるか試してみるしかありませんね。

回答No.9

Wendy02

こんばんは。

・食事に行ったときの金額の求め方。
1：男だけで行った時は割り勘
？2：男女で行った時は男5、女4の比率で払う
　　　↓
2. 男女で行った時は男6、女4の比率で払う

このときの男が支払う金額と女性が支払う金額は？
(入力条件：男の数、女の数、支払い合計額)

これって、二次方程式ではないでしょうか？
以下は、計算自体が危ないかもしれませんが、このような作り方をします。

>ユーザー定義関数は、１つの戻り値しか返すことはできません。
という回答がありますが、それは誤解です。配列数式の場合は、その選択したセルの数だけ解を返します。

'標準モジュール

Function GotoDutch(Bill As Variant, Optional M As Integer = 0, Optional W As Integer = 0) As Variant
Dim ar(1, 0) As Double
Dim Money As Long
Dim Tm As Long '男側の全額
Dim Tw As Long '女側の全額
'男と女の比率
Const MEN As Double = 0.6
Const WOMEN As Double = 0.4

If StrComp(TypeName(Bill), "RANGE", vbTextCompare) = 0 Then
　Money = Bill.Value
Else
　Money = Bill
End If

If M = 0 Or W = 0 Then
　Tm = Bill
　GotoDutch = Int(Tm / (M + W) + 0.5)
Else
　ar(0, 0) = Int(Bill / (M + W * (WOMEN / MEN)) + 0.5)
　ar(1, 0) = Int(ar(0, 0) * (WOMEN / MEN) + 0.5)
　GotoDutch = ar()
End If
End Function

この数式は、配列数式の場合と、一般関数の場合の二面性を持っていまする

この関数の配列の使い方は、上下の二つのセルを選択して、F2を押して、Cntrl キーを押しながら、Shift + Enter　とします。これを配列の確定と呼びます。

これを外すときは、二つのセルを選択して、Ctrl キーを押しながら、Enter を押します。

=GOTODUTCH(A1,3,3)

GOTODUTCH(支払い金額,男性の数,女性の数）

とすると、二つのセルに別々の金額が出てきます。ただし、四捨五入されていますから、合計金額は、多少大目に出ます。

また、片方しかいない場合は、男女比率は出てきませんので、通常の入力の方法をします。（配列確定はしません）

=GOTODUTCH(A1,6)

とします。

なお、私の#6 マクロの

>「Public fRng As Range」でTrue or Falseを判定しているのですよね？
は、数式のセル位置を取得しています。

数式のある場所から、Offset(2,2)の場所に出力するようになります。
> If fRng.Value = True Then
> fRng.Offset(2, 2).Value = 100
> Else
> fRng.Offset(2, 2).Value = 0
> End If

ということになります。

お礼

回答ありがとうございます。
参考にして自分でも考えてみます。

補足

>また、片方しかいない場合は、男女比率は出てきませんので、通常の入力の方法をします。（配列確定はしません）

ということは、条件によって関数の使い方(選択するセルの数など)が
異なるってことですよね？
私の例が適切でないからなのですが
条件分岐は、関数内で計算してみないとわからないのです。
つまり初めから男だけか男女混合かがわかっていない状態で
求めれるようにしたいのです。

入力条件だけを入力して、あとは勝手に計算してもらうためには
条件によってアクティブにするセル(orセル数)が変わってしまうのは
ちょっと困るのです。

ですが、配列を使うというのは新しいアイデアですね。
これをうまく使ってなんとかできないか考えてみます。

回答No.8

Yes_No_F

zap35のこの回答への補足をふまえ、

自分なら、男が支払う金額・女が支払う金額
の両方を算出出来るユーザー関数を作成します。
(入力条件：男の数、女の数、支払い合計額、男or女)
としといて、「男or女」の部分に
1を入力→男が支払う金額
2を入力→女が支払う金額
としておきます。

if 男or女 = 1 then
金額 = 男が支払う金額
else
金額 = 女が支払う金額
end if

として金額を算出します。この時の「男or女」は別セルの値を参照する様にする。もちろんフラグを立てるのは手入力ですが。

作成の仕方としては、その方がシンプルと思いますけど

いかがでしょうか？

お礼

回答ありがとうございました。

補足

フラグを手入力にすることが面倒なので
ALL 自動化したくて質問させていただいております。
こちらの説明不足でしたね。申し訳ありません。

回答No.6

Wendy02

こんばんは。

掲示されたサンプルのマクロでは、ユーザー定義関数が、単に、呼び出しだけですから、値を排出も確保もされてないので、意味はありませんが、同じようなマクロで、私は、今試してみたのですが、どうやら、この方法ですと、ユーザー定義関数に、戻って来れないので、ユーザー定義関数にエラーが出てしまうようです。この手のマクロは、ひじょうに特殊なマクロで、一般的には不可能だとされますが、実際は可能です。

しかし、一般的に、関数のみで監視状態にするには、DDL で、DDE やOLEにしてあげないと出来ません。例えば、コントロールツールなどでは、値を排出しますが、それは、OLEになっているからです。

それは、Excel内だけでは不可能ですから、代用するには、以下のようにします。

'標準モジュールにユーザー定義関数を置きます。

'サンプル:引数が、偶数か奇数か判定する
Public fRng As Range
Function testFunction(rng As Range)
On Error Resume Next
If rng.Value Mod 2 = 1 Then
　testFunction = True
Else
　testFunction = False
End If
End Function

'シートモジュール
Private Sub Worksheet_Calculate()
If fRng Is Nothing Then Exit Sub
　If fRng.Value = True Then
　　fRng.Offset(2, 2).Value = 100
　Else
　　 fRng.Offset(2, 2).Value = 0
　End If
End Sub

ただし、ひとつだけ問題があります。このCalculate イベントは、非常におせっかいで、常に、値が変わるたびに、イベントマクロが走ります。それも、他のシートモジュールで発生しても、このイベントマクロが走りますので、デバッグモードのときは、ひじょうにうっとうしいですから、デバッグモードの時は、このイベントは、コメントブロックをつけたほうがよいです。

それから、このマクロは、初心者向けでもなければ、上級者向けでもありません。いわゆる実験のマクロで、実用性は乏しいように思っています。ある有名なVBAのプログラマが、これとは違う方法で、ご自身のホームページで発表していたはずです。多少のメカニズムでも公開しなければ、何の意味もありませんけれどね。（^^;

本来は、ワークシートの製作の段階で、そのようなことにならないようにする必要があります。また、値を入れるなら、このような必要はありません。関数や条件付書式で十分です。

お礼

回答ありがとうございます。
引き続きフォローをいただけると光栄です。
よろしくお願いします。

補足

なにやら難しい話で頭がついていけてません。。。。

まず「Public fRng As Range」でTrue or Falseを判定しているのですよね？
これはどういう意味があるのでしょうか？
私の質問に対する回答として、どの部分に位置するものなのかが
私には難しくてわからないのですが。。。

「Private Sub Worksheet_Calculate()」では上記の関数で
Trueか否かで入力する値が異なるということですよね？
ただここで「fRng.Offset(2, 2).Value 」の意味がよく理解できません。
fRngのOffsetとなると、どのセルに値を入力することになるのでしょうか？
また「fRng」の値を与える場所がありませんが、どのように値を与えるのでしょうか？

回答No.5

zap35

他の回答者さまも書かれているようにFUNCTIONでは１つの戻り値しか返すことはできません。でも結果と途中の判断結果の２つの状態を返したいのですか。

姑息的な方法ですが100という結果値に対して
・途中結果がTrueなら10000を加えて10100を返し、Falseなら100を返す。結果値が10000以上なら２行２桁横に値をセットする。
・先頭にフラグを着けて返す。TrueならT100、FalseならF100

いずれも本当の結果値(100)を取り出すのは一手間かけなければなりませんが、この方法なら２つの状態を取り出せます。

補足

この場合ですと、計算結果もそのまま使えませんね。
私が実現したいのは、例えばこういう場合の計算方法です。

----------------------------------------------------------------
・食事に行ったときの金額の求め方。
1：男だけで行った時は割り勘
2：男女で行った時は男5、女4の比率で払う

このときの男が支払う金額と女性が支払う金額は？
(入力条件：男の数、女の数、支払い合計額)
----------------------------------------------------------------

ユーザー定義関数では、男の支払い額を求めることとします。
男だけの時は、簡単に求められます。
しかし、男女で行った場合は男と女それぞれの支払額を求める必要があります。
しかし、あくまでユーザー定義関数で返す値は男の支払額。
よって、女がいるときは別セルにフラグを立てる(例えば1の値を入れる)
ことによって、フラグが立った場合に自動に計算するようにしたい。
(IF([フラグを立てたセル]=1,[ユーザー定義関数],"")みたいにしておけばできますよね？)

このとき、男女で食事に行った場合は、ユーザー定義関数内で条件分けされて
計算されていることから(男だけの場合と男女の場合とで)
関数内では男だけか男女かはわかっています。
(そして男だけの場合と男女の時とで、別の計算方法で男の支払額を算出している)

よって男女で食事に行った時はユーザー定義関数で計算しているセルから
相対的に一定の距離にあるセル(ですからActiveCell.Offsetなんです)
にフラグ(1と言う値)を立てたいのです。
そうすることで、男女で食事に行った時は二つのセルにそれぞれ支払額が出てきます。
男だけなら、男の支払額だけ出てくるようになります。

このような場合の時に、どうやってフラグを立てるか(別のセルに1という値を入れるか)？
が質問の本質なのです。

ご助力お願いします。

回答No.4

Yes_No_F

Option Explicit

Sub test1()
ActiveCell.Offset(2, 2).Value = test2(1, 2)
End Sub

Private Function test2(a As Integer, b As Integer) As Integer
test2 = a + b
End Function
--------------------------------------------------
たとえば上記を実行してみて下さい。1+2の処理を計算
する場合の処理の記述例です。
このソースの計算過程の数値を獲得したい場合は、下記の様に変更。
--------------------------------------------------
Option Explicit
Private X As Integer
Private Y As Integer

Sub test1()
Dim i As Integer
i = test2(1, 2)
MsgBox X
MsgBox Y
ActiveCell.Offset(2, 2).Value = i
End Sub

Private Function test2(a As Integer, b As Integer) As Integer
X = a
Y = b
test2 = a + b
End Function
--------------------------------------------------
「Private X As Integer」と「Private Y As Integer」
を宣言しておく事によって計算過程の数値を拾う事が出来ます。
この辺の処理を利用すれば、どうでしょうか？

お礼

回答ありがとうございます。
今後もよろしければお力をお貸しください。
よろしくお願いします。

補足

この例ですと、私が求めている結果と逆になってしまいます。
どういうことかと申しますと
私はtest2を実行してtest1と同様の効果が得られる方法
もしくは、test2を実行してtest1を実行させる方法を求めています。
ですので、上記の例ですとtest1を実行する必要があることから
実行するマクロが逆になってしまいます。
(求める動作：test2を実行→test1が動作)
(回答の動作：test1を実行→test2が動作)

回答No.3

noname#35109

状況が全然わかっていない者です...。

'----------------------------
Sub test1()
ActiveCell.Offset(2, 2).Value = 100
End Sub
'----------------------------
Function test2()
test1
End Function
'----------------------------
Sub test3()
test2
End Sub
'----------------------------

として，
test3 を実行させたら，
test2 経由で
test1 が実行できましたが...。

これでは，ダメなのでしょうね......?????? 。

お礼

回答ありがとうございます。

そうですね、これではダメなんです。
まず、「あるセルでユーザー定義関数で計算する」
が前提です。
この例ですと「test3」ではなく「test2」を実行したときに
test1と同様の効果が得られる方法を求めているわけです。

回答No.2

ham_kamo

No.1の方も書いてますが、Functionはあくまでも関数なので、セルの値を書き換えたり、という処理はできません。test2()は値を返すだけの処理にしましょう。

それで、ユーザ定義関数でなく一般の関数の場合もそうですが、他のセルの値によってあるセルの値をコントロールするのであれば、そのセルに数式を入れます。

質問文の例の場合、B3に =test2() という数式が入っているのであれば、２行２列右下、つまりD5に
=IF(B3=○○,100,"")
のように書くとよいと思います。B3に$をつけずに相対参照にしておけば、２行２列、という位置関係はセルをコピーしても保たれます。

お礼

回答ありがとうございます。
よろしければ引き続きフォローをお願いします。

補足

それでは希望の結果が得られないんです。
というのも、ユーザー定義関数で返す値によって
条件分岐しているのではなく
あくまで関数内での計算過程で求められた結果で
条件分けしているからです。

回答No.1

Yes_No_F

test2マクロは、ユーザー関数の処理として考えてます？
Functionプロシージャは値渡しを目的とする場合の処理
ですので、処理を実行させる事は無理ですよ。

2行2列隣のセルに数式を入れておく事での対応は無理ですか？

お礼

回答ありがとうございます。
無理なんですね。わかりました。
では実際にどのように処理すればよいのでしょうか？