Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

return array from function in VBA

Tags:

All,

I would like to write a function to return an array of integers so I can index them, but I am not aware of the syntax for VBA. Here is the pseudo code:

function getStats() as integer     dim returnVal(4) as integer     returnVal(0)=c2percent14     returnVal(1)=c3percent14     returnVal(2)=c4percent14     returnVal(3)=c5percent14     getStats=returnVal end function  msgbox getStats(3) 

where these values are all integers, or should be, and then I can index the return array for the stat that I want. Thanks.

-Rik

like image 366
Rik Avatar asked Jul 22 '15 13:07

Rik


People also ask

Can you return an array from a function VBA?

A function in a normal module (but not a Class module) can return an array by putting () after the data type. Note that what is returned is actually a copy of the array inside the function, not a reference. So if the function returns the contents of a Static array its data can't be changed by the calling procedure.

How do you access an array in VBA?

Using a For Loop allows quick access to all items in an array. This is where the power of using arrays becomes apparent. We can read arrays with ten values or ten thousand values using the same few lines of code. There are two functions in VBA called LBound and UBound.


1 Answers

Give the function the type as an array:

function getStats() as Integer()         dim returnVal(0 to 3) as integer      returnVal(0) = c2percent14     returnVal(1) = c3percent14     returnVal(2) = c4percent14     returnVal(3) = c5percent14      getStats = returnVal end function  Sub mysub()    Dim myArray() As Integer     myArray = getStats()     msgbox myArray(3) end sub  
like image 178
moffeltje Avatar answered Oct 14 '22 09:10

moffeltje