Length of Array question

**welchs101** · 12-23-2008, 09:00 AM

I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array. For example: Suppose i have 10 strings in the array. Is there a command to determine that there are only 10 elements in the 100 possilble array?

**dominicb** · 12-23-2008, 09:13 AM

Good afternoon welchs101

Originally Posted by welchs101

I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array.

You would have to loop through the array and test each string, and count at which point an empty string is returned.

FWIW I think you're using arrays incorrectly. The idea is that if you don't know how big your array's going to be you set it up using empty parnetheses. Then everytime you add an element to the array you use Redim Preserve to re-dimension the array and keep the previous contents of the array unchanged :

Sub ArrayTest()
Dim test()
For n = 1 To 10
ReDim Preserve test(n)
test(n) = n
Next n
MsgBox UBound(test)
End Sub

The last UBound statement will return how many elements are in your array - you can change the value of 10 to anything - the array will always expand to accept the next value.

HTH

DominicB

**welchs101** · 12-23-2008, 09:22 AM

I had not thought of doing arrays in this fashion. Thanks.

**welchs101** · 12-23-2008, 06:26 PM

i tried what was suggested but i am getting an error.......subset of code is shown below:

Sub get_HW_Quiz_cols(Gradebook_HW_col() As String, Inputfile_HW_col() As String, Gradebook_quiz_col() As String, Inputfile_quiz_col() As String, _
Gradebook_Filename As String, HW_InputFilename As String, HW_InputFile_Sheet As String)

Dim temprange As Range
Dim temp As Integer

'get HW and Quiz cols from Gradebook
Set temprange = Workbooks(Gradebook_Filename).Worksheets(Gradebook_Sht_Nm_HW).Range("1:1")

For z = 1 To Max_HW_grades
temp = Find_next_occurance(temprange, "HW" & z, "col")
If temp <> 0 Then
ReDim Preserve Gradebook_HW_col(z)
Gradebook_HW_col(z) = alphacol(temp)
End If
Next z

'get HW and Quiz cols from Gradebook
Set temprange = Workbooks(HW_InputFilename).Worksheets(HW_InputFile_Sheet).Range("1:1")

For z = 1 To Max_HW_grades
temp = Find_next_occurance(temprange, "HW" & z, "col")
If temp <> 0 Then
ReDim Preserve Inputfile_HW_col(z)
Inputfile_HW_col(z) = alphacol(temp)
End If
Next z

End Sub

error occurs on the ReDim statements..........

**shg** · 12-23-2008, 06:38 PM

Array must be dynamic when originally declared to be resized, e.g.,

Sub x()
    Dim a() As String
    ReDim a(1 To 20)
    
    y a
    MsgBox UBound(a)
End Sub

Sub y(a() As String)
    ReDim Preserve a(1 To 10)
End Sub