Help with Arrays

[oaishm]

It's a good thing the message board doesn't charge by the question. Here's another question. I have this procedure that makes an array and includes the statements

colcount = Column_input.Rows.Count - 1

by the way column_input is a range

redim array1(colcount)
newfunction(array1)

etc.

Then in the new function I have
function newfunction(array1)

by the way
newfunction(array1())
didn't work

anyway, I try to make this

array1(2) = 5
but I get subscript out of range error

But, when I go array1(2,1)=5

All is well in the world.

Why would excel voluntarily make these redimming decisions so vicariously that I must personify my computer?

 

[Richard Schollar]

Hi

Can you show us what your full code is so we can see all the definitions and steps?

 

[oaishm]

Sub testratio()
Call ratio(Worksheets("PReq"), [RInput], 6, 7, [PInput], 2)
End Sub
Sub ratio(Out_worksheet As Worksheet, Column_input As Range, coloffset As Integer, unit As Integer, Row_input As Range, rowoffset As Integer)
Dim colarray() As Variant 'Use this to copy range into an array for column
Dim colarray2() As Variant 'use this for the col names
Dim colarray3() As Variant 'use this for the col units
Dim rowarray() As Variant 'use this to copy range into an array for row
Dim rowarray2() As Variant 'use this for row names
Dim colcount As Integer 'number of records in columninput
Dim rowcount As Integer 'number of records in rowinput
colcount = Column_input.Rows.Count - 1
rowcount = Row_input.Rows.Count - 1
ReDim colarray(colcount)
ReDim colarray2(colcount)
ReDim colarray3(colcount)
ReDim rowarray(rowcount)
ReDim rowarray2(rowcount)
Out_worksheet.Select
Out_worksheet.Range(Cells(13, 5), Cells(600, 6)).Delete
Out_worksheet.Rows(12).Delete
colarray = Column_input.Resize(colcount, 1)
colarray2 = Column_input.Resize(colcount, 1).Offset(0, coloffset - 1)
colarray3 = Column_input.Resize(colcount, 1).Offset(0, unit - 1)
rowarray = Row_input.Resize(rowcount, 1)
rowarray2 = Row_input.Resize(rowcount, 1).Offset(0, rowoffset - 1)
Out_worksheet.Range(Cells(13, 5), Cells(colcount + 12, 5)) = colarray
testarray = concatenatevector(colarray2, colarray3)
Out_worksheet.Range(Cells(13, 6), Cells(colcount + 12, 6)) = testarray
Out_worksheet.Range(Cells(11, 7), Cells(11, rowcount + 6)) = Application.Transpose(rowarray)
Out_worksheet.Range(Cells(12, 7), Cells(12, rowcount + 6)) = Application.Transpose(rowarray2)
ActiveWorkbook.Names.Add Name:=Out_worksheet.Name, RefersToR1C1:="=" & Out_worksheet.Name & "!R13C7:R" & 13 + colcount & "C" & 6 + rowcount - 1
End Sub
Function concatenatevector(array1, array2)
Dim result() As Variant
minimum = WorksheetFunction.Min(UBound(array1), UBound(array2))
ReDim result(minimum)
For x = 1 To minimum
    result(x) = array1(x, 1) & " " & array2(x, 1)
    Next x
concatenatevector = result
End Function

Here it is, I hope this is the best way to deliver all this code on this board.

 

[oaishm]

While perusing you'll note that I put in the testarray thing. For some reason all the other dimensions are variant(1 to 15, 1 to 1) while testarray is variant (0 to 15). I get why test array is 0 to 15. I can change that. I don' get why it's not 1 to 1 and why all the other ones are 1 to 1 and why the fact that testarray isn't 1 to 1 means it can't be pasted back on tthe range like all the other array.

I've ready every book on this, but there isn't a whole lot there.

 

[Richard Schollar]

Hi

In these lines:

colarray = Column_input.Resize(colcount, 1)
colarray2 = Column_input.Resize(colcount, 1).Offset(0, coloffset - 1)
colarray3 = Column_input.Resize(colcount, 1).Offset(0, unit - 1)
rowarray = Row_input.Resize(rowcount, 1)
rowarray2 = Row_input.Resize(rowcount, 1).Offset(0, rowoffset - 1)

You are assigning ranges to your variant array variables - ie you are effectively redimming the arrays as 2D arrays (whose dimensions always start at 1 and not 0). Thus you get a sub out of range error if you only specify one dimension when you try and assign a value to the array.

 

[oaishm]

Friends, I've figured it out. It only took four hours. So, here's a warning to beginners, if they find this so helpful. This information isn't anywhere else on the first three pages of google search for excel vba arrays.

When an array is one dimensional, it is horizaontal

dim array1(8)

if we dim array1() and then copy a range like array1 = range("a1:a30") it will become a 30x1 array, a two dimensional array that has an otherwise useless second dimension.

Thus, we must use array1(x,1) with the ,1 portion to acquiesce to the 2d requirements of a vertical array.

I guess making a one dimensional array horizontal makes sense to database developers because records are horizontal on a table, but for people that took linear algebra, this is the exact opposite from expectation.
This only took all morning to figure out. Thank god this isn't a profession