Not able to execute a function - ByRef argument type mismatch

Hello dax2ib,

When arguments are passed by reference, they must have been declared using the DIM statement or be assigned to objects that already exist, like a Range on a worksheet. All Ranges objects are passed as 2-D arrays even if it only a single cell. The important thing to remember is a Range is an Object array. This means you will have full access to all the methods and properties of a Range object. A standard array will only contain values, not objects.

You can convert a Range into a standard 2-D array easily. See the code below:

Please Login or Register  to view this content.

It is important to add the Value property to the Range in this case. This will tell VBA to convert the Range to an array. You can assign a 2-D Array to a range also, just reverse the arguments. This will overwrite formulas but not cell formatting.

1.

Please Login or Register  to view this content.

2. You can't do: VarA(1) = VarA(2) = VarB(1) = VarB(2) = VarC(1) = VarD(2) = 1 in VBA; each array's element must be initialized separately.

how do you call the function?