Can you directly write the contents of an array in excel VBA to a CSV file?-CodePudding

I have some code which loads a table into an array, does some calculations, creates a new sheet, writes the changed array to the new sheet, and saves that new sheet as a CSV.

I need a CSV because that is the only format the destination system this is for accepts.

I was wondering if it is possible to directly write the array to a CSV skipping 2 steps.

Through my research I found the below code from

What I cant figure out is when I watch MyArray in the watch window it is populated the way it is supposed to be so I cannot figure out this error. Any help is appreciated

CodePudding user response：

You need to understand the difference between a Variant and an Array of Variant
A Variant variable in VBA can hold anything, and that includes an array. An Array is always an Array:

Dim v1 as Variant
v1 = 3                   ' Now v1 holds an Integer
Redim v1(1 to 10)        ' ...and now it holds an array of Variant
v1(1) = 3                ' The first member of v1 now holds an Integer 
v1 = "VBA is a miracle"  ' Now v1 holds a string (and is no longer an array)

Dim v2() as Variant      ' v2 is an array of Variant per declaration
Redim v2(1 to 10)        ' Assign 10 members to v2
v2(1) = 3                ' The first member of v1 now holds an Integer 
v2 = "VBA is a mystery"  ' This is not possible as v2 is an array!
                         ' (Compiler error "Can't assign to an array)

If you declare your Sub with parameter MyArray() As Variant, you are saying that your function expects an Array of Variants as parameter. In your call SaveAsCSV arOut, Application.ActiveWorkbook.Path & "\Demo.csv", you are passing the variable arOut as argument.
This variable is most likely declared as Variant (not as Array of Variant), and therefore you get the first compiler error: A variant is not an Array of Variant. The compiler simply cannot know that during runtime, arOut will be an array. It could also be an Integer or String or anything else.

Now you changed the parameter of your routine declaration to MyArray As Variant and your call gets valid, the compiler no longer complains, it assumes that MyArray can be anything. If you assign a number or string to it, the Sub will be called anyhow (of course it will not work).

Within the routine, you write UBound(MyArray(), 1). The compiler again complains because MyArray is not an Array. By changing the call to UBound(MyArray, 1), the compiler is happy. If MyArray doesn't hold an array, you will get a Runtime error (Type mismatch).

So using MyArray as Variant in the function declaration and removing the parenthesis when using it in UBound solves your issue, everybody is happy.

With a little drawback: As already mentioned, the compiler cannot protect you from calling the Sub with an invalid argument. As a programmer, you want the compiler to catch as many problems as possible. So an alternative would be to declare arOut as Array:

Dim arOut() as Variant
arOut = Range("A1:F10")  ' Do whatever magic you need to fill the array.

Now you can go back to the declaration Public Sub SaveAsCSV(MyArray() As Variant.... And using UBound(myArray(), 1) will work, too. However, you can (and should) write UBound(myArray, 1) in any case.