Array varying length Run-time error 9

[phill86]

Hi

I have a field that stores a recurrence pattern that is imported from outlook

example recurrence pattern
(3,20101208,10:00:00,1,0,4,120,2,20110331,12:00:00 ,)

The pattern is not always the same length so it could have up to 13 elements.

I need to split this field up and assign the different elements to variables I have done this using an array

I am getting Run-time error 9 because the array is not always the same length

using the above example I get the error on the following line

MonthOfYear = aStringArray(11)
 

Here is the code I am using

 
Dim aStringArray() As String 'array of strings
 
 
 
        RecPattern = RstCheckRec!RecPattern ' this the outlook recurrence pattern stored in the table I need to seperate these elements
        aStringArray = Split(RecPattern, ",") 'split string up
 
 
 
       'assigns the variables with the values from the the recurrence pattern stored in the table
 
        RecType = aStringArray(0)
        StartDate = aStringArray(1)
        StartTime = aStringArray(2)
        Interval = aStringArray(3)
        NoEndDate = aStringArray(4)
        Occurrences = aStringArray(5)
        Duration = aStringArray(6)
        Instance = aStringArray(7)
        EndDate = aStringArray(8)
        EndTime = aStringArray(9)
        DayOfMonth = aStringArray(10)
        MonthOfYear = aStringArray(11)
        Regenerate = aStringArray(12)
        DayOfWeekMask = aStringArray(13)
 

Many Thanks

Phill

[jimatqsi]

Let's see what is in your array after the split:

dim intCount, intMax as integer.
dim strMessage as string
 
intMax = Ubound(aStringArray)
strMessage = "The array contains: " & vbCRLF
 
for intCount=0 to intMax
strMessage = strMessage & intCount & " " &  aStringArray(intCount) & vbCRLF
next
 
msgbox strMessage  ' will show the contents of your array
 

Jim

[ADezii]

@phill86
As I see it, the key is to:

1. Populate a variant Array (varStringArray) with all the Elements (Comma Delimited) in the String Variable recPattern.
2. Calculate the Number of Elements that are 'Missing' up to a MAXIMUM of 14, not 13.
3. Re-Dimension the Array, while maintaining existing Values, and fill the Missing Elements with the String 'Missing'. You can use whatever String you wish.
4. The following Code Demo assumes recPattern has 10 Delimited Values in the String. Results are also posted along with the Code.
   

   Dim varStringArray As Variant
   Dim recPattern As String
   Dim intNumOfElements As Integer
   Dim bytMissingNumOfElements As Byte
   Dim bytCounter As Byte
   Const conMAX_NUM_OF_ELEMENTS As Byte = 14     'NOT 13
    
   '12 Test Elements
   recPattern = "3,20101208,10:00:00,1,0,4,120,2,20110331,12:00:00"
    
   varStringArray = Split(recPattern, ",") 'split string up
    
   'Determine the Number of Elements in the Variant Array
   intNumOfElements = UBound(varStringArray) + 1
    
   If intNumOfElements = 0 Or intNumOfElements > 14 Then Exit Sub
    
   'How many Elements of the Array are 'Missing'?
   bytMissingNumOfElements = conMAX_NUM_OF_ELEMENTS - intNumOfElements
    
   'Fill the Missing Elements of the Array with a String. Do this 
   'by Redimensioning the Array while at the same time maintaining 
   'its contents
   For bytCounter = intNumOfElements To conMAX_NUM_OF_ELEMENTS - 1
     ReDim Preserve varStringArray(UBound(varStringArray) + 1)
     varStringArray(UBound(varStringArray)) = "Missing"
   Next
    
   'Assigns the Variables with the values from the the recurrence pattern stored in
   'the Table, noting any Elements that are Missing
   RecType = varStringArray(0)
     Debug.Print RecType
   StartDate = varStringArray(1)
     Debug.Print StartDate
   StartTime = varStringArray(2)
     Debug.Print StartTime
   Interval = varStringArray(3)
     Debug.Print Interval
   NoEndDate = varStringArray(4)
     Debug.Print NoEndDate
   Occurrences = varStringArray(5)
     Debug.Print Occurrences
   Duration = varStringArray(6)
     Debug.Print Duration
   Instance = varStringArray(7)
     Debug.Print Instance
   EndDate = varStringArray(8)
     Debug.Print EndDate
   EndTime = varStringArray(9)
     Debug.Print EndTime
   DayOfMonth = varStringArray(10)
     Debug.Print DayOfMonth
   MonthOfYear = varStringArray(11)
     Debug.Print MonthOfYear
   Regenerate = varStringArray(12)
     Debug.Print Regenerate
   DayOfWeekMask = varStringArray(13)
     Debug.Print DayOfWeekMask

OUTPUT:

3
20101208
10:00:00
1
 
4
120
2
20110331
12:00:00
Missing
Missing
Missing
Missing

[phill86]

Hi

Thanks for the reply the solution from ADezii worked many thanks

Phill

[ADezii]

Line # 16 should be:

If intNumOfElements = 0 Or intNumOfElements > conMAX_NUM_OF_ELEMENTS Then Exit Sub