1

I have a couple of basic questions about using arrays in VBA, which I have not found the answer to.

Firstly, is there a way to specify a one dimensional row vector in Excel VBA?

I am new to using arrays in Excel VBA, and have been defining row and column vectors as two dimensional arrays, as in the examples below

'Define a Local Row Vector
ReDim aArray(1 to iCntRows, 1 to 1)

'Define a Local Column Vector
ReDim aArray(1 to 1, 1 to iCntCols)

So secondly, is this best practice? Should I be using a one dimensional array instead?

Thanks Tim

Improve this question
3
  • 1
    Why on Earth you want to use 2D array for ONE row/column? Commented Feb 21, 2013 at 18:09
  • 7
    because Excel ranges are 2-dimensional Commented Feb 21, 2013 at 22:32
  • Thanks Charles. I think that is why I started using them! Commented Feb 22, 2013 at 14:30

3 Answers 3

Reset to default
1

Where you intend to copy the array to a range, like

Range(somerange) = aArray

Then , yes, use 2d arrays

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Chris, I often read a sheet range into an array, manipulate it and write it back to the sheet. However, I am also using 2d arrays in procedures that just read the range from the sheet, to manipulate other arrays. Would you store these as 1d arrays?
1

You have to use the transpose function to convert from a 2d array.

new_array = Application.Transpose(Sheet1.Range("A2:A100").Value)

where new_array is a 1d array.

Improve this answer

Comments

1 
Sub tt()

    Const SZ As Long = 1000
    Dim arr(), x

    ReDim arr(1 To SZ)
    For x = 1 To SZ
        arr(x) = x
    Next x

    'add to sheet (as a row)
    Sheet1.Range("a1").Resize(1, SZ).Value = arr

    'add to sheet (as a column)
    'note there is an upper limit when using transpose of around 65k
    Sheet1.Range("a5").Resize(SZ, 1).Value = Application.Transpose(arr)

End Sub
Improve this answer

2 Comments

Hi Tim, would you mind adding some comments to explain what your procedure is doing? I am not sure what to learn from this. Thanks, Tim
You asked about using 1D arrays. My example was just to illustrate how they can be used, an what to watch out for (that a 1D array needs to be transposed before it can be assigned to a column-shaped range)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.