String Array declared, but the elements are seen as Integer (<Subscript out of range>)

[Daniela_S]

Hello,


I'm not experienced in VBA, just trying to compare multiple DATE type data between two cells (each one containing multiple DATE type data with a separator supposed to be ALT+ENTER, I'm using CLEAN function on the cells values before) and I came to the conclusion that I need a macro for splitting the multiple dates in arrays for comparison afterwards element by element.

I am using MS Excel 2007.

My issue is that even if the Array is declared as String (and displayed as String() type during debugging), I've observed that the Arrays elements (e.g. Array_test1(0), Array_test1(1) a.s.o. for the function below) are seen as Integer type values, at debug is raised Run-Time error "9" "Subscript out of range".

I've checked for several hours some information on internet, in each example found for String type arrays it seems very easy to initialize the values for each element of the array, but I did not succeeded.

I'm supposing that maybe there is some subtility for initializing the String array elements ... but I'm lost ... any suggestion is welcome.



The code looks like (many Public variables, as I thought would help me to ease the debugging, I'll change them to Dim variables in Function MULTIDATE for the final version) :

Public L1 As Integer
Public L2 As Integer
Public C1 As Integer
Public i As Integer
Public Msg As String

 
Function MULTIDATE(String1 As String, String2 As String) As Boolean

  Dim Array_test1() As String
  Dim Array_test2() As String
  L1 = Len(String1)
  L2 = Len(String2)
  If L1 <> L2 Then Msg = "Different dates number"
  
  MULTIDATE = True

' Each date has 10 characters, C1 is the counter of number of dates in the cell
  C1 = L1 / 10

  For i = 0 To C1 - 1
    [COLOR=#ff0000]Array_test1(i) = Left(String1, 10)
    Array_test2(i) = Left(String2, 10)

[/COLOR]    String1 = Right(String1, L1 - i * 10)
    String2 = Right(String2, L2 - i * 10)

    If Array_test1(i) < Array_test2(i) Then MULTIDATE = False
  Next i

End Function

Thanks in advance for your help.


Kind regards,
Daniela

 

[RoryA]

Hi, and welcome to the forum.

You haven't sized your arrays so you can't loop through them.

If your data is delimited by alt+enter you can just use split to populate them:

Array_test1 = split(string1, chr(10))

for example.

 

[Daniela_S]

Thanks a lot, Rory ! Your suggestion works perfectly. Have a wonderful week