By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

Array varying length Run-time error 9

100+
P: 121
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

Expand|Select|Wrap|Line Numbers
  1. MonthOfYear = aStringArray(11)
  2.  
Here is the code I am using

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim aStringArray() As String 'array of strings
  3.  
  4.  
  5.  
  6.         RecPattern = RstCheckRec!RecPattern ' this the outlook recurrence pattern stored in the table I need to seperate these elements
  7.         aStringArray = Split(RecPattern, ",") 'split string up
  8.  
  9.  
  10.  
  11.        'assigns the variables with the values from the the recurrence pattern stored in the table
  12.  
  13.         RecType = aStringArray(0)
  14.         StartDate = aStringArray(1)
  15.         StartTime = aStringArray(2)
  16.         Interval = aStringArray(3)
  17.         NoEndDate = aStringArray(4)
  18.         Occurrences = aStringArray(5)
  19.         Duration = aStringArray(6)
  20.         Instance = aStringArray(7)
  21.         EndDate = aStringArray(8)
  22.         EndTime = aStringArray(9)
  23.         DayOfMonth = aStringArray(10)
  24.         MonthOfYear = aStringArray(11)
  25.         Regenerate = aStringArray(12)
  26.         DayOfWeekMask = aStringArray(13)
  27.  
Many Thanks

Phill
Jul 13 '10 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,240
Let's see what is in your array after the split:

Expand|Select|Wrap|Line Numbers
  1. dim intCount, intMax as integer.
  2. dim strMessage as string
  3.  
  4. intMax = Ubound(aStringArray)
  5. strMessage = "The array contains: " & vbCRLF
  6.  
  7. for intCount=0 to intMax
  8. strMessage = strMessage & intCount & " " &  aStringArray(intCount) & vbCRLF
  9. next
  10.  
  11. msgbox strMessage  ' will show the contents of your array
  12.  
Jim
Jul 13 '10 #2

ADezii
Expert 5K+
P: 8,705
@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.
    Expand|Select|Wrap|Line Numbers
    1. Dim varStringArray As Variant
    2. Dim recPattern As String
    3. Dim intNumOfElements As Integer
    4. Dim bytMissingNumOfElements As Byte
    5. Dim bytCounter As Byte
    6. Const conMAX_NUM_OF_ELEMENTS As Byte = 14     'NOT 13
    7.  
    8. '12 Test Elements
    9. recPattern = "3,20101208,10:00:00,1,0,4,120,2,20110331,12:00:00"
    10.  
    11. varStringArray = Split(recPattern, ",") 'split string up
    12.  
    13. 'Determine the Number of Elements in the Variant Array
    14. intNumOfElements = UBound(varStringArray) + 1
    15.  
    16. If intNumOfElements = 0 Or intNumOfElements > 14 Then Exit Sub
    17.  
    18. 'How many Elements of the Array are 'Missing'?
    19. bytMissingNumOfElements = conMAX_NUM_OF_ELEMENTS - intNumOfElements
    20.  
    21. 'Fill the Missing Elements of the Array with a String. Do this 
    22. 'by Redimensioning the Array while at the same time maintaining 
    23. 'its contents
    24. For bytCounter = intNumOfElements To conMAX_NUM_OF_ELEMENTS - 1
    25.   ReDim Preserve varStringArray(UBound(varStringArray) + 1)
    26.   varStringArray(UBound(varStringArray)) = "Missing"
    27. Next
    28.  
    29. 'Assigns the Variables with the values from the the recurrence pattern stored in
    30. 'the Table, noting any Elements that are Missing
    31. RecType = varStringArray(0)
    32.   Debug.Print RecType
    33. StartDate = varStringArray(1)
    34.   Debug.Print StartDate
    35. StartTime = varStringArray(2)
    36.   Debug.Print StartTime
    37. Interval = varStringArray(3)
    38.   Debug.Print Interval
    39. NoEndDate = varStringArray(4)
    40.   Debug.Print NoEndDate
    41. Occurrences = varStringArray(5)
    42.   Debug.Print Occurrences
    43. Duration = varStringArray(6)
    44.   Debug.Print Duration
    45. Instance = varStringArray(7)
    46.   Debug.Print Instance
    47. EndDate = varStringArray(8)
    48.   Debug.Print EndDate
    49. EndTime = varStringArray(9)
    50.   Debug.Print EndTime
    51. DayOfMonth = varStringArray(10)
    52.   Debug.Print DayOfMonth
    53. MonthOfYear = varStringArray(11)
    54.   Debug.Print MonthOfYear
    55. Regenerate = varStringArray(12)
    56.   Debug.Print Regenerate
    57. DayOfWeekMask = varStringArray(13)
    58.   Debug.Print DayOfWeekMask
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 3
  2. 20101208
  3. 10:00:00
  4. 1
  5.  
  6. 4
  7. 120
  8. 2
  9. 20110331
  10. 12:00:00
  11. Missing
  12. Missing
  13. Missing
  14. Missing
Jul 13 '10 #3

100+
P: 121
Hi

Thanks for the reply the solution from ADezii worked many thanks

Phill
Jul 13 '10 #4

ADezii
Expert 5K+
P: 8,705
Line # 16 should be:
Expand|Select|Wrap|Line Numbers
  1. If intNumOfElements = 0 Or intNumOfElements > conMAX_NUM_OF_ELEMENTS Then Exit Sub
Jul 13 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.