Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

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

like image 386
ANd Avatar asked Mar 03 '11 19:03

ANd


People also ask

What does constant expression required mean in VBA?

This error has the following causes and solutions: You tried to initialize a constant with a variable, an instance of a user-defined type, an object, or the return value of a function call.

What is a constant expression?

A constant expression is an expression that contains only constants. A constant expression can be evaluated during compilation rather than at run time, and can be used in any place that a constant can occur.

How do you solve a constant error required in Java?

The fix is simple; change the Foo.BA* variable declarations to have initializers that are compile-time constant expressions. In other examples (where the initializers are already compile-time constant expressions), declaring the variable as final may be what is needed.

How do you declare a dynamic array in VBA?

Declare a dynamic array By declaring a dynamic array, you can size the array while the code is running. Use a Static, Dim, Private, or Public statement to declare an array, leaving the parentheses empty, as shown in the following example. Use the ReDim statement to declare an array implicitly within a procedure.


1 Answers

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"

like image 180
RichardTheKiwi Avatar answered Oct 10 '22 16:10

RichardTheKiwi