Error in Excel/VB function: "Constant expression required"

30
votes

I'm new at VB and I'm having a hard time doing something that should be very simple.

I'm trying to make an array of n+1 length and I keep getting "Constant expression required" when running the following code

Function binomial(n As Integer, p As Double)
Dim probabilities(0 To n) As Double
End Function

I understand that the arguments used to build the array must be constants, but do I create one from the argument of the function?

Thank you in advance

1
excelvba

1 Answers

65
votes

You can't DIM against a variable size. ReDim it instead

For example

Function binomial(n As Integer, p As Double)
Dim probabilities() As Double
ReDim probabilities(0 To n)
MsgBox LBound(probabilities)
MsgBox UBound(probabilities)
End Function

Sub test()
Call binomial(3, 2)
End Sub

Run the sub "test"