sunshinemaio.blogg.se - Function in visual basic

Why wouldn't you simply put all of your code in one place? Subroutines are useful because they eliminate redundancy. You may ask yourself why subroutines would even be used. What you were actually doing was adding code to this subroutine. This is where you have been inserting your test code before. For example, the Form_Load subroutine is automatically called when a form loads. Some subroutines are called automatically when certain actions are performed. Therefore, one subroutine can "call" another subroutine. When the code in a subroutine is executed, the subroutine is said to be "called". Unlike a variable, a subroutine doesn't hold data. A subroutine has a name attributed with it, much like a variable does. Subroutines can be thought of as miniature programs. In VBA you cannot assign values to an array at an index until you declare the length.What Are Subroutines? The temp array has to be declared with length.Any attempt to ReDim getStats as an array will also throw an error. You cannot assign values to the function name (getStats) like you normally would in a function you can only assign the temporary array to the function name once you have assigned all the values to the temp array.

You have to use a temporary array in the function.

Even if you declare the array in the sub as a Variant and the function returns an Integer array that will not work either.

The function that returns an array has to be of EXACTLY same type.

The array declared in the calling sub has to be of undeclared length.

These are required for your function, assignment, and return to work: Although VBA is usually pretty lax, this particular thing is very picky. The comments I included here are very important. Remember that an array declared (2) will hold 3 values, 0-2. 'returnVal(3) = 3 This will throw an error. I'm going to add an answer here because I'm happy to say, after hours of frustration and bad information, I finally know how to return arrays! Here is how you return an array from a function: Sub mysub()ĭim myArray() As Integer 'if you declare a size here you will get "Compile error, can't assign to array"įunction getStats() As Integer() 'The return type must be EXACTLY the same as the type declared in the calling sub.ĭim returnVal(2) As Integer 'if you DON'T declare a size here you will get "Run-time error '9': Subscript out of range"