VBA Functionプロシージャで複数の値を

質問

エクセルでVBAのFunctionプロシージャについて複数の値を戻り値として
受け取る方法を考えています。

下記のようなサンプルプログラムを作りました。
---------------------------------------------------------------------------------
Option Explicit

Private Sub CommandButton1_Click()
Dim kekka(3) As Double
Dim txt As String
txt = "12.12A,34.34B,56.56C,78.78D"
kekka(0) = test(txt)
End Sub

Public Function test(ByVal text As String) As Double
Dim txt_kakou(3) As String

'## 渡された「txt = "12.12A,34.34B,56.56C,78.78D"」を加工し、数値のみを取り出し
'## 下記のように配列txt_kakou(3)に振り分ける
'## 加工方法は割愛

txt_kakou(0) = 12.12
txt_kakou(1) = 34.34
txt_kakou(2) = 56.56
txt_kakou(3) = 78.78

'string型からdouble型に変換する。
txt_kakou(0) = CDbl(txt_kakou(0))
txt_kakou(1) = CDbl(txt_kakou(1))
txt_kakou(2) = CDbl(txt_kakou(2))
txt_kakou(3) = CDbl(txt_kakou(3))

test = txt_kakou()

End Function

-----------------------------------------------------------------------------------

まず、呼び出し側のプロシージャでkekka(3)という配列を宣言します。
次に変数txtに「"12.12A,34.34B,56.56C,78.78D"」の文字列を代入します。
そして変数txtをFunctionプロシージャに投げます。

Functionプロシージャでは、受け取った「"12.12A,34.34B,56.56C,78.78D"」
を数値のみに分解し、4つの変数に代入します。
この4つの変数も配列で用意し、txt_kakou(3)とします。

このtxt_kakou(3)を呼び出し元に返し、呼び出しもとの変数kekka(3)に入れたいのです。

また、はじめ変数txtに代入される値は文字列ですが、この文字列をFunctionプロシージャで分解し、
分解した値は、最終的には数値として扱いたいので、途中でdouble型に変換しています。

これを実行すると、Functionプロシージャの最後のtest = txt_kakou()で、型が一致しません
といわれてしまいます。

どのように書き直せばいいのでしょうか。

よろしくお願いします。

回答No.3

piroin654 · ベストアンサー

No2です。関数を以下に変更してください。
それこそエラーが出ないのでうっかり。

Private Function test(ByVal text As String, ByVal i As Integer) As Double
Dim txt_kakou(3) As String
Dim int_kakou(3) As Double

'## 渡された「txt = "12.12A,34.34B,56.56C,78.78D"」を加工し、数値のみを取り出し
'## 下記のように配列txt_kakou(3)に振り分ける
'## 加工方法は割愛

txt_kakou(0) = "12.12"
txt_kakou(1) = "34.34"
txt_kakou(2) = "56.56"
txt_kakou(3) = "78.78"

int_kakou(0) = CDbl(txt_kakou(0))
int_kakou(1) = CDbl(txt_kakou(1))
int_kakou(2) = CDbl(txt_kakou(2))
int_kakou(3) = CDbl(txt_kakou(3))

test = int_kakou(i)
End Function

お礼

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

こういう方法があったんですね。勉強になります。
また、Debug.Printなどもしらない機能だったので、今後
役に立ちそうです。ぱっと調べたところですが、これってあくまで確認するための機能で、
プログラム自体には何の影響も及ぼさないのですよね。

＞何がしたいか…
質問文のコードは質問用に簡略したもので、意味のないプログラムのように見えますが、
実際に今考えているプログラムでは、文字と数値の混ざった文字列がいくつもあり、
それを文字と数値に分けなければなりません。その作業をFunction関数で行えば、コードを
いくつも書かなくてすみます。

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

回答No.8

waterline123

No.１です。後で思い出したのですが、No.5さんに突っ込まれてますね。

>Public Function test(ByVal text As String) As Double()
で戻り値を配列にする事はできると思いますが。。。
Variant の使用は極力避ける事をお勧めします。

No.5さんのおっしゃるとおりです。
自分が普段使わないので思い込みが入ってました。
失礼しました。

お礼

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

いえいえ、ありがとうございます。
別の手法もわかったので、機会があったら使いたいと思います。

回答No.7

MARU4812

私は VB 系で10年ご飯を食べてきている職業プログラマです。
考えは古臭いかもしれませんが、堅実なコードを書けると思って
います。

その経験からの助言です。
Debug.Print と MsgBox では影響が異なります。
特異な状況のみこの違いが問題になるのであれば、問題が
起こってから対処するのでも良いでしょうが、簡単に起こり
得るので書き込みました。
VB はイベント駆動型のプログラミングですので、どの位置に
コードを書いても、イベントとは密接に関係します。
Debug.Print でデータの確認をすることを覚えたのであれば、
MsgBox は利用しないことをお勧めします。
(具体的な理由は前述。)

プログラミングでは1行コードを書いた時点でもう、結果が
同じという事はありません。それが支障をきたすか問題なしか
は別として、必ず何らかの影響があります。それを正しく
把握し、支障が無いように最大限の注意を払うべきです。

VisualBasic に限りませんが、やはり本家は実行ファイル(EXE)
を作成する環境でしょう。VB.NET では、Debug 時と Release
時は扱いが違い、Release(製品出荷)時は Debug クラスを利用
したコードはコンパイル時点で削除されます。VBA ではその
ような概念がありませんので、デバッグ用のコードは本番環境
まで残り続けます。この違いが分かりますか？

デバッグ用のコードとはいえ、1行命令を書けばコストが
掛かります。処理時間を余分に消費します。それだけなら
「細かい事を言わなくても」って思いますか？テキスト
出力する時点で結構重たい処理ですけどね。

では、Debug.Printの引数にオブジェクトを指定した場合
でも同じですか？

プログラムで破棄処理まで行なったオブジェクトの中身を
参照した場合に、特定の条件で VB が勝手にデフォルト
インスタンスを生成してしまう事があるのをご存知ですか？
値を参照するだけのデバッグ用のコードがバグを生む事さえ
そう珍しい事ではないのですよ？

開発環境が整備され、プログラミングの敷居が低くなったと
いえ、プログラムを扱う時点で重要な事は変わっていないと
思います。少なくとも簡単に「支障ない」ということは難しい
はずです。
初心者用に簡略化した説明をするならまだ分かりますが、常套
手段だという理由だけで、動作の異なるコマンドを等しく
「支障ない」と言い切るのは信じられませんでした。

回答No.6

piroin654

ボタンのクリックイベントで、
MsgBoxやDebug.Printをどの位置で使っているか
よく確認せずにコメントが入っていますが。

VB.NETが何の本家か知りませんが、Debug.Print
やメッセージボックスを途中データの確認に
使うことは何の支障もありません。支障が
あるような使い方、あるいは支障があるなら
そのとき考えればよろしいかと。

このような手法は他の開発言語、たとえば
C言語などでもprintf関数を使ってデータの
推移の確認にすることは常套手段です。

あまり、他の回答者のコメントをいじくるのは
好きではないのでここでENDにします。

回答No.5

MARU4812

> じゃあFunctionが配列にできるかというと出来ません。
Public Function test(ByVal text As String) As Double
↓
Public Function test(ByVal text As String) As Double()
で戻り値を配列にする事はできると思いますが。。。
Variant の使用は極力避ける事をお勧めします。

>＞プログラム自体には何の影響も及ぼさないのですよね。
>はい、まったく。コード表を開くのが面倒ならば、
>Debug.Print kekka(0) のかわりに、
>MsgBox kekka(0)
本家の VB.NET だと Debug 表記はビルド時の動作に影響があります。
Release では必要なくなること前提に使います。
http://www5b.biglobe.ne.jp/~yone-ken/VBNET/special/sp05_DebugWrite.html
中間言語の話とかになるので VBA では関係無さそうですが。

また、MsgBox による確認は、処理の流れを止めてしまいますので、
イベントのデバッグなどには向きません。「MsgBox を入れたがために
イベントの起こる順番が変わる」といった事が起こりえます。
(フォーカスがダイアログに移ったり、意識してないイベントが処理されたり
しています。)

＞実際に今考えているプログラムでは、文字と
＞数値の混ざった文字列がいくつもあり、
＞それを文字と数値に分けなければなりません。
参考にするなら、IsNumeric 関数がありますね。
配列で全ての答えが返ってくるより、1個々判定できた方が使い勝手が
いいという設計ですが。
まぁ、"2E3"とかも数値に変換できる文字列として扱われてしまいます
けどね。
# CDbl も同様。

お礼

回答ありがとうございます。
確かにvariant型は使わないほうがいい名とは思っていましたが、
それしか通用しなかったもので…。

Public Function test(ByVal text As String) As Double()
にすればよかったのですね。

Debug.Print kekka(0) に関してなにやら議論がなされていますが、
私としては良くわからないので、ノーコメントで。

回答No.4

piroin654

＞プログラム自体には何の影響も及ぼさないのですよね。
はい、まったく。コード表を開くのが面倒ならば、
Debug.Print kekka(0) のかわりに、

MsgBox kekka(0)

＞実際に今考えているプログラムでは、文字と
＞数値の混ざった文字列がいくつもあり、
＞それを文字と数値に分けなければなりません。
なるほど。むしろこのほうがメインですね。

回答No.2

piroin654

実行する前に最初からコンパイルすると、
test = txt_kakou()
で型が合わない、とエラーが出ると思いますが。
理由はNo1の方と同じです。

何がしたいのかよくわかりませんが、
CommandButton1_Clickの中身から推察して、

Private Sub コマンド0_Click()
Dim kekka(3) As Double
Dim txt As String
txt = "12.12A,34.34B,56.56C,78.78D"
kekka(0) = test(txt, 0)
Debug.Print kekka(0)
End Sub

Private Function test(ByVal text As String, ByVal i As Integer) As Double
Dim txt_kakou(3) As String
Dim int_kakou(3) As Double

'## 渡された「txt = "12.12A,34.34B,56.56C,78.78D"」を加工し、数値のみを取り出し
'## 下記のように配列txt_kakou(3)に振り分ける
'## 加工方法は割愛

txt_kakou(0) = 12.12
txt_kakou(1) = 34.34
txt_kakou(2) = 56.56
txt_kakou(3) = 78.78

txt_kakou(0) = CDbl(txt_kakou(0))
txt_kakou(1) = CDbl(txt_kakou(1))
txt_kakou(2) = CDbl(txt_kakou(2))
txt_kakou(3) = CDbl(txt_kakou(3))

test = txt_kakou(i)

End Function

のようなものになると思いますが。

回答No.1

waterline123

Private Sub CommandButton1_Click()
Dim kekka(3) As Double
Dim txt As String
txt = "12.12A,34.34B,56.56C,78.78D"
kekka(0) = test(txt)
上記を下記のように修正（配列をパラメータで渡している）
⇒call test(txt,kekka(0),kekka(1),kekka(2),kekka(3))

End Sub

Public Function test(ByVal text As String) As Double
上記を下記のように修正（パラメータの受け取り口を作る。厳密にはFunctionである必要はないけど、別に修正する必要もないのでそのままです。）
⇒Private Function test(ByVal text As String,pdlbkekka1 as Double
,pdlbkekka2 as Double,pdlbkekka3 as Double,pdlbkekka4 as Double)

txt_kakou(0) = CDbl(txt_kakou(0))
txt_kakou(1) = CDbl(txt_kakou(1))
txt_kakou(2) = CDbl(txt_kakou(2))
txt_kakou(3) = CDbl(txt_kakou(3))
上記を下記のように修正（パラメータで渡された変数に代入してるだけ）

pdlbkekka1 = CDbl(txt_kakou(0))
pdlbkekka2 = CDbl(txt_kakou(1))
pdlbkekka3 = CDbl(txt_kakou(2))
pdlbkekka4 = CDbl(txt_kakou(3))

下記を削除（そもそも落ちている原因）
test = txt_kakou()

これで動くと思います。
なぜ落ちてるかと言うと、
test = txt_kakou()
代入しようとする値は配列変数ですが、test自体は配列じゃないから型不一致で落ちてます。
（testは単なるdoubleですよね）
じゃあFunctionが配列にできるかというと出来ません。
なので、パラメータで変数として渡して代入した方が早いです。

もちろん他にも方法はありますが、手っ取り早くという観点で記載しました。

お礼

回答ありがとうございます。
なるほど、パラメータとして変数を渡す方法もあるのですね。

質問した後、いろいろいじくってたら、下記の方法でできるようです。
変数「kekka」、「kakou(3)」、「Functionプロシージャの型」をvariant型で
宣言しますと、型が一致しませんのエラーが起こらず、配列のまま値を返してくれることがわかりました。
ちなみに、最終的な値を格納する変数として、「ATAI(3)」をdouble型で新たに用意しました。
下記のコードです。これって何か問題あるのでしょうか。

------------------------------------------------------------
Option Explicit

Private Sub CommandButton1_Click()
Dim kekka As Variant
Dim ATAI(3) As Double
Dim txt As String

txt = "12.12A,34.34B,56.56C,78.78D"
kekka = test(txt)

ATAI(0) = kekka(0)
ATAI(1) = kekka(1)
ATAI(2) = kekka(2)
ATAI(3) = kekka(3)

End Sub

Public Function test(ByVal text As String) As Variant

　　Dim txt_kakou(3) As Variant

'渡された「txt = "12.12A,34.34B,56.56C,78.78D""」を加工し、数値のみを取り出し
'下記のように配列txt(3)に振り分ける
'加工方法は割愛

txt_kakou(0) = 12.12
txt_kakou(1) = 34.34
txt_kakou(2) = 56.56
txt_kakou(3) = 78.78

test = txt_kakou

End Function

-----------------------------------------------------------------