Array indexes

I'm using VB in Excel. Trying to use an array, the size of which is set with a variables. For example:

Dim intNumRows As Integer
intNumRows = 10
Dim strCellNumber(intNumRows) As String

When I run it, it throws an error, wanting the index to be a constant.
When I replace the variable intNumRows with a number (constant) it works. This is quite a limitation to not be able to size the array while running.
What am I missing?

I am using this strCellNumber array variable to put numeric values into specific cells using For Next loops. For instance:

For intRowCounter = 1 To 5
strCellNumber(intRowCounter) = "C" & CStr(intRowCounter)
Range(strCellNumber(intRowCounter)).Value = intRowCounter * 2
Next intRowCounter

This is in a Click event, if it matters. I want the user to define the size of the array. I'm thinking that maybe I can define the index constant by getting the index value from a particular cell that user puts a value in.
Years past, I did a lot of Microsoft BasicA programming. Just getting started with VB.
Thanks in advance for any help.



Comments

  • : I'm using VB in Excel. Trying to use an array, the size of which is set with a variables. For example:
    :
    : Dim intNumRows As Integer
    : intNumRows = 10
    : Dim strCellNumber(intNumRows) As String
    :
    : When I run it, it throws an error, wanting the index to be a constant.
    : When I replace the variable intNumRows with a number (constant) it works. This is quite a limitation to not be able to size the array while running.
    : What am I missing?
    :
    : I am using this strCellNumber array variable to put numeric values into specific cells using For Next loops. For instance:
    :
    : For intRowCounter = 1 To 5
    : strCellNumber(intRowCounter) = "C" & CStr(intRowCounter)
    : Range(strCellNumber(intRowCounter)).Value = intRowCounter * 2
    : Next intRowCounter
    :
    : This is in a Click event, if it matters. I want the user to define the size of the array. I'm thinking that maybe I can define the index constant by getting the index value from a particular cell that user puts a value in.
    : Years past, I did a lot of Microsoft BasicA programming. Just getting started with VB.
    : Thanks in advance for any help.
    :
    :
    :
    :
    Just tried defining the index constant:
    Const NumRows As Integer = Range("A10").Value
    which doesn't work.


  • : : I'm using VB in Excel. Trying to use an array, the size of which is set with a variables. For example:
    : :
    : : Dim intNumRows As Integer
    : : intNumRows = 10
    : : Dim strCellNumber(intNumRows) As String
    : :
    : : When I run it, it throws an error, wanting the index to be a constant.
    : : When I replace the variable intNumRows with a number (constant) it works. This is quite a limitation to not be able to size the array while running.
    : : What am I missing?
    : :
    : : I am using this strCellNumber array variable to put numeric values into specific cells using For Next loops. For instance:
    : :
    : : For intRowCounter = 1 To 5
    : : strCellNumber(intRowCounter) = "C" & CStr(intRowCounter)
    : : Range(strCellNumber(intRowCounter)).Value = intRowCounter * 2
    : : Next intRowCounter
    : :
    : : This is in a Click event, if it matters. I want the user to define the size of the array. I'm thinking that maybe I can define the index constant by getting the index value from a particular cell that user puts a value in.
    : : Years past, I did a lot of Microsoft BasicA programming. Just getting started with VB.
    : : Thanks in advance for any help.
    : :
    : :
    : :
    : :
    : Just tried defining the index constant:
    : Const NumRows As Integer = Range("A10").Value
    : which doesn't work.
    :
    :
    :
    Hi,
    Const NumRows As Integer = Range("A10").Value
    is not const value :)

    Use dynamic array and Redim for change it's size
    [code]Dim intNumRows As Integer
    Dim strCellNumber() As String
    intNumRows = 10
    ReDim strCellNumber(intNumRows) [/code]
    If you want to save original values, use RedimPreserve strCellNumber{intNumRows)

    Hope this helps

    [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]

    [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories