Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Variables To An array Throughout a loop
Hi,
I'm trying to assign a value to an array in the following procedure and it is telling me 'Subscript out of range at the statement MyArray (i) =whichchar. How can I do this differently to continue to assign variables to this array throughout the loop? Private Sub ParseData(MyWord As String) Dim I As Integer, MyStep As Integer, LastLetter As String, NumChar As Integer, WhichChar As String Dim numOccurences As Integer, T As Integer Dim MyArray() As String MyStep = 1 I = 0 NumChar = 0 Do Until I = 1 LastLetter = Mid(MyWord, MyStep, 1) MyStep = MyStep + 1 Select Case LastLetter Case Is = "" I = I + 1 End Select DoEvents Loop NumChar = MyStep - 2 I = 1 T = 1 Do While I <= NumChar WhichChar = Mid(MyWord, I, 1) Do Until T = NumChar + 1 Select Case Mid(MyWord, T, 1) Case Is = WhichChar numoccurrences = numoccurrences + 1 T = T + 1 Case Else T = T + 1 End Select Select Case T Case Is = NumChar + 1 MyArray(I) = WhichChar Debug.Print "There are " & numoccurrences & " " & WhichChar & "'s in this word" End Select Loop T = 1 I = I + 1 numoccurrences = 0 Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Variables To An array Throughout a loop
You declared the MyArray() array as a dynamic array (nothing between the
parentheses), but you didn't tell VB how many elements it will have. You do that with a ReDim statement. Looking at your code, I *think* you will need to add this line to your code... ReDim MyArray(1 To Len(MyWord)) which will tell VB to reserve one element for each letter in the word being passed into the subroutine. However, in looking at your code, you never use the MyArray array, so I'm not sure why you created it or are attempting to populate it. Also, you declared a variable with the name numOccurences, but then went on to misspell it in the rest of your code. Based on what the code you wrote does, you took a very complicated route to achieve it. Here is a much shorter (and more efficient) subroutine which does what your code currently does (note that I did not make use of the MyArray array because you didn't)... Sub ParseData(MyWord As String) Dim X As Long Dim WhichChar As String Dim numOccurences As Long For X = 1 To Len(MyWord) WhichChar = Mid(MyWord, X, 1) numOccurences = Len(MyWord) - Len(Replace(MyWord, WhichChar, "")) Debug.Print "There are " & numOccurences & " " & _ WhichChar & "'s in this word" Next End Sub I would like to make a suggestion to you that you become more familiar with the various functions and statements that VB has to offer. Doing that will allow you to write more focused code in the future. -- Rick (MVP - Excel) "R Tanner" wrote in message ... Hi, I'm trying to assign a value to an array in the following procedure and it is telling me 'Subscript out of range at the statement MyArray (i) =whichchar. How can I do this differently to continue to assign variables to this array throughout the loop? Private Sub ParseData(MyWord As String) Dim I As Integer, MyStep As Integer, LastLetter As String, NumChar As Integer, WhichChar As String Dim numOccurences As Integer, T As Integer Dim MyArray() As String MyStep = 1 I = 0 NumChar = 0 Do Until I = 1 LastLetter = Mid(MyWord, MyStep, 1) MyStep = MyStep + 1 Select Case LastLetter Case Is = "" I = I + 1 End Select DoEvents Loop NumChar = MyStep - 2 I = 1 T = 1 Do While I <= NumChar WhichChar = Mid(MyWord, I, 1) Do Until T = NumChar + 1 Select Case Mid(MyWord, T, 1) Case Is = WhichChar numoccurrences = numoccurrences + 1 T = T + 1 Case Else T = T + 1 End Select Select Case T Case Is = NumChar + 1 MyArray(I) = WhichChar Debug.Print "There are " & numoccurrences & " " & WhichChar & "'s in this word" End Select Loop T = 1 I = I + 1 numoccurrences = 0 Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Variables To An array Throughout a loop
On Dec 16, 11:27*pm, "Rick Rothstein"
wrote: You declared the MyArray() array as a dynamic array (nothing between the parentheses), but you didn't tell VB how many elements it will have. You do that with a ReDim statement. Looking at your code, I *think* you will need to add this line to your code... ReDim MyArray(1 To Len(MyWord)) which will tell VB to reserve one element for each letter in the word being passed into the subroutine. However, in looking at your code, you never use the MyArray array, so I'm not sure why you created it or are attempting to populate it. Also, you declared a variable with the name numOccurences, but then went on to misspell it in the rest of your code. Based on what the code you wrote does, you took a very complicated route to achieve it. Here is a much shorter (and more efficient) subroutine which does what your code currently does (note that I did not make use of the MyArray array because you didn't)... Sub ParseData(MyWord As String) * Dim X As Long * Dim WhichChar As String * Dim numOccurences As Long * For X = 1 To Len(MyWord) * * WhichChar = Mid(MyWord, X, 1) * * numOccurences = Len(MyWord) - Len(Replace(MyWord, WhichChar, "")) * * Debug.Print "There are " & numOccurences & " " & _ * * * * * * * * *WhichChar & "'s in this word" * Next End Sub I would like to make a suggestion to you that you become more familiar with the various functions and statements that VB has to offer. Doing that will allow you to write more focused code in the future. -- Rick (MVP - Excel) "R Tanner" wrote in message ... Hi, I'm trying to assign a value to an array in the following procedure and it is telling me 'Subscript out of range at the statement MyArray (i) =whichchar. *How can I do this differently to continue to assign variables to this array throughout the loop? Private Sub ParseData(MyWord As String) Dim I As Integer, MyStep As Integer, LastLetter As String, NumChar As Integer, WhichChar As String Dim numOccurences As Integer, T As Integer Dim MyArray() As String MyStep = 1 I = 0 NumChar = 0 Do Until I = 1 * *LastLetter = Mid(MyWord, MyStep, 1) * *MyStep = MyStep + 1 * *Select Case LastLetter * * * *Case Is = "" * * * * * *I = I + 1 * *End Select * *DoEvents Loop NumChar = MyStep - 2 I = 1 T = 1 Do While I <= NumChar * *WhichChar = Mid(MyWord, I, 1) * * * *Do Until T = NumChar + 1 * * * * * *Select Case Mid(MyWord, T, 1) * * * * * * * *Case Is = WhichChar * * * * * * * * * *numoccurrences = numoccurrences + 1 * * * * * * * * * *T = T + 1 * * * * * * * *Case Else * * * * * * * * * *T = T + 1 * * * * * *End Select * * * * * *Select Case T * * * * * * * *Case Is = NumChar + 1 * * * * * * * * * *MyArray(I) = WhichChar * * * * * * * * * *Debug.Print "There are " & numoccurrences & " " & WhichChar & "'s in this word" * * * * * *End Select * * * *Loop * * * *T = 1 * * * *I = I + 1 * * * *numoccurrences = 0 Loop End Sub- Hide quoted text - - Show quoted text - the len() function is what I was looking for...How silly of me...by the way, I did use the myarray in the following section of the above - posted code... Select Case T Case Is = NumChar + 1 MyArray(I) = WhichChar Debug.Print "There are " & numoccurrences & " " & WhichChar & "'s in this word" End Select |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Variables To An array Throughout a loop
the len() function is what I was looking for...How silly of me... by the way, I did use the myarray in the following section of the above - posted code... Select Case T Case Is = NumChar + 1 MyArray(I) = WhichChar Debug.Print "There are " & numoccurrences & " " & WhichChar & "'s in this word" End Select Yes, but other than assigning values to it, you never made use the array again. -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning cell values to variables | Excel Discussion (Misc queries) | |||
Assigning Cell data to VB Variables | Excel Programming | |||
Assigning cell address components to variables | Excel Programming | |||
Assigning 10x1 array to 2nd collumn of 10x3 array | Excel Programming | |||
Assigning 10x1 array to 2nd collumn of 10x3 array | Excel Programming |