question

LaiKanLeon-4557 avatar image
0 Votes"
LaiKanLeon-4557 asked Viorel-1 answered

What is the difference between Public vArray as Variant and Public vArray() as Variant?

Hello,

I am developing a program in Excel VBA.

My code is found in 2 places: Module1 and UserForm1.

In Module1, I have the following declarations:

 Option Explicit
    
 Public vDbaseArray2_1D() As Variant
 Public vDbaseArray1_2D() As Variant
    
 Public vIntermediate1_1D() As Variant
 Public vIntermediate2_1D As Variant   
 Public sMatch As String    

The program works perfectly.

But if I change the before-last line to:
Public vIntermediate2_1D() As Variant

the program crashes. The crash occurs on this line in UserForm1:

vIntermediate2_1D = Filter(vIntermediate1_1D, sMatch, True, vbTextCompare)


The message is:

Run-time Error '13'
Type Mismatch


I could ignore the problem since my program is working correctly, but I would like to know...
I think it is possible to declare an array with / without ().
My array is 1-dimensional.


Thanks for any help that can enlighten me, or orient me to find the reason.

Leon










office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Find returns an array of string, therefore you can define ‘vIntermediate2_1D( ) As String’. The Variant can be used to hold various data types, including arrays; therefore it works in case of ‘vIntermediate2_1D As Variant’ too. This value can be then assigned to an array: 'Dim a() As String: a = vIntermediate2_1D’.

However a variable like ‘vIntermediate2_1D( ) As Variant’ cannot be used to represent an array of strings. Your code proves that an automatic conversion of array of string to array of Variant is not supported. If you need such conversion, you can write a loop.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.