配列を返すVBAユーザー定義関数の書き方

質問

VBAで、商と余りを返す整数割算のユーザー定義関数を書こうとしています。セルA1に割られる数、B1に割る数が書いてあり、C1とD1に「=xdiv("A1:B1")」をCtrl+Shift+Enterで入力します。C1に商、D1に余りを返すようにしたいと思ってます。
以下のように書いてみたのですが、戻り値の書き方がどうもよくわかりません…
Function xdiv(ByRef d() As Integer) As Integer
　　.............
End Function
よろしくお願いします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

Function Xdiv(arg As Variant) As Integer()
'配列出力の方法
Dim Divd As Double '被除数
Dim Divsr As Double　'除数
Dim ret(1, 0) As Integer
If TypeName(arg) = "Range" Then
　Divd = arg.Cells(1)
　Divsr = arg.Cells(2)
ElseIf TypeName(arg) = "Variant()" Then
　Divd = arg(1)
　Divsr = arg(2)
End If
　ret(0, 0) = Divd \ Divsr
　ret(1, 0) = Divd Mod Divsr
　Xdiv = ret()
End Function

＞C1とD1に「=xdiv("A1:B1")」をCtrl+Shift+Enterで入力します。

正確には、この式をC1に入れて、次に、範囲をD1にまで伸ばしてから、Ctrl + Shift + Enter で確定します。念のため、解除の仕方は、同じく範囲を選択してから、F2 を押してから、Ctrl + Enterです。

なお、これは、\, Mod を使っていますから、Long型以上の解は出てきません。また、二つ以上のセルを入れても、最初のセルと次のセルしか引数として使われません。

お礼

回答ありがとうございます。
ElseIf TypeName(arg) = "Variant()"
により{=Xdiv({523,3})}という書き方にも対応できるということですね。勉強になりました。

回答No.2

BLUEPIXY

Public Function xdiv(a As Long, b As Long)
Dim ret(2)
Dim q As Long, r As Long

q = Fix(a / b)
r = a - b * q
ret(0) = q
ret(1) = r
xdiv = ret
End Function
'この場合、望むような結果になるのは、横方向です

お礼

回答ありがとうございます。
以下の内容で解決することができました。

エクセル側
C1,D1セル　{=xdiv(A1:B1)}

VBA側
Public Function xdiv(ar As Variant) As Variant
　　Dim ret(2) As Variant
　　xdiv = Array(Int(ar(1) / ar(2)), ar(1) Mod ar(2))
End Function

回答No.1

o_chi_chi

関数は設定したセルにしか結果を返せないと
思うのですが。
パラメタにするか関数を分けるかになるかと
思います。

お礼

回答、ありがとうございます。
でも、TRANSPOSE()関数などは結果を配列で返しているので仕組みとしては存在してると思うのですが…　それをなんとかVBAで書きたいと思ってます。