Adding values to an array in vba

Question

I have an array in vba in excel. This array is a set size, it doesnt need to be variable.

For the life of me I don't know what I'm doing wrong in trying to add values to the array. My code as it currently stands is :

Dim count As Integer
Dim name(１ to 361) As String
Dim tmp As String

Sub go()

For count = 2 To 10
    name(count) = cells(7,count)
Next count

End Sub

I've tried all manner of variations on this. cells(7,count).value, name(count).value, etc... etc...

But I keep getting errors telling me I'm missing an as or there's a generic syntax error.

Its really annoying to be falling at this first hurdle before i can even begin to do anything useful with the array. This is pushing me to finding an arrayless solution but that would be working around the problem rather than actually tackling it.

chris neilsen · Accepted Answer · 2014-03-07 04:27:32Z

1

You first issue is that go is a reserved word. Choose another name from your Sub. Same applies to name. There are also several other issues, see below

' Dont use key words as procedure names
Sub MySub()
    ' Declare as procedure scope
    '  unless there is a very good reason to use module scope

    ' dont use key words as variable names
    Dim cnt As Long ' Integer ' use Long by default
    Dim nm(1 To 361) As String
    Dim tmp As String

    For cnt = 2 To 10
        nm(cnt) = Cells(7, cnt)
    Next cnt
End Sub

answered Mar 7, 2014 at 4:27

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

3 Comments

Doug Glancy Over a year ago

Go is a reserved word? I know GoTo and Resume are, but please remind me how Go is used.

chris neilsen Over a year ago

@doug don't know why, but try it with and without go and you'll see

Doug Glancy Over a year ago

Chris, I made the other reserved word changes, but left the Sub name as "Go", and it worked.

John Alexiou · Accepted Answer · 2014-03-07 06:12:55Z

1

I suggest using a dynamic array assignment

Sub go()
    Dim i as Long, N as Long
    Dim names() as Variant
    ' It will be initialized to names(1 to rows, 1 to columns)

    N = 10
    names = Range("RefToFirstCell").Resize(1,N).Value2

    For i=1 to N
        Debug.Print names(1,i)
    Next i

    'If you want to write back to the worksheet use `Range().Resize().Value2 = names`.

End Sub

answered Mar 7, 2014 at 6:12

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

2 Comments

chris neilsen Over a year ago

Op specifically states This array is a set size, it doesnt need to be variable. One would assume (perhaps incorrectly) that Op has a reason for fixing the size.

John Alexiou Over a year ago

The OP says the array "is a fixed size". This does not mean, it has to be fixed. It just is right now. I think it is fixed as a matter of convenience, of which I disagree.

Collectives™ on Stack Overflow

Adding values to an array in vba

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related