I have created a UDF in Excel VBA that returns a 2 dimensional array, in certain cases the length of the array should be zero. How do you define a zero length array?
The below returns a compiler error;
Dim empty_array(1 to 1,1 to -1)
Asked
Active
Viewed 264 times
1
-
Dim myArray(,) as WhateverYourDataTypeIs e.g. Dim myArray(,) as Integer – Absinthe Jul 17 '17 at 08:21
-
Try `Dim var As Variant: var = Array(Array(), Array())` – Robin Mackenzie Jul 17 '17 at 08:28
-
@Absinthe That would be VB.NET. – GSerg Jul 18 '17 at 08:15
-
2Would it not be enough to return an unresized array? Excel will display `#VALUE!` in all cells, but I believe it would do the same if you passed a `(1 To -1)` array. If you mean to consume that array later with code as opposed to putting it on a sheet, you might want to see https://stackoverflow.com/q/183353/11683. – GSerg Jul 18 '17 at 08:19
-
@GSerg, I agree, returning a CVErr makes sense and it is consistent with the user's past excel experience – user425678 Jul 18 '17 at 08:40
-
Not a `CVErr`, just an empty array (`Dim empty_array()`). – GSerg Jul 18 '17 at 08:44
-
@GSerg - Nope, that's VBA syntax for sure. I've used it hundreds of times. – Absinthe Jul 18 '17 at 15:33
-
@Absinthe Excel 2010 VBA throws a syntax error with that – user425678 Jul 18 '17 at 16:02
-
@GSerg - Apologies, your right, sorry... too many programming languages ;) – Absinthe Jul 18 '17 at 17:19