Visual Basic - simple 1-dimensional array with Strings giving syntax error [duplicate]

Question

I want to create an one-dimensional array with Strings and I tried this code:

Dim sheets_names_array() As String = { "Sheet1", "Sheet2" }

but I get a 'Compile error: Syntax error' when trying to run the code.

What is my error? Many thanks, Electra

try Dim sheets_names_array As String new row sheets_names_array = array("Sheet1", "Sheet2") — NoobVB
– NoobVB, Commented Sep 23, 2022 at 10:01
thanks a lot, it works but now I have a hard time iterating through it :-/ do you know what is the right code to loop through it? thanks so much! — Electra
– Electra, Commented Sep 23, 2022 at 10:10
You should share a bit more code in question, as Dim sheets_names_array() As Variant is also possible declaration — NoobVB
– NoobVB, Commented Sep 23, 2022 at 10:11
Dim sheets_names_array() As String sheets_names_array = Array("Sheet1", "Sheet2") For Each sheet As String In sheets_names_array MsgBox (sheet & " ") Next End Sub the first line of the loop gives a syntax error — Electra
– Electra, Commented Sep 23, 2022 at 10:24
also, read a bit about LBound and UBound, - these functions are very commonly used while looping through arrays. — NoobVB
– NoobVB, Commented Sep 23, 2022 at 10:39

NoobVB · Accepted Answer · 2022-09-23 10:24:37Z

1

here is correct example of your approach:

Public Sub PrintArray()

' make an array with the sheet names we want to parse
Dim sheets_names_array() As Variant, sheet As Variant

sheets_names_array = Array("Sheet1", "Sheet2")

' loop the sheets array
For Each sheet In sheets_names_array
   Debug.Print (sheet & " ")
Next

End Sub

and this one is usual loop through each sheet as you declare sheet variable anyway:

Sub Test()

' Declare ws as a worksheet object variable.
Dim ws As Worksheet

' Loop through all of the worksheets in the active workbook.
For Each ws In ThisWorkbook.Worksheets
    
   Debug.Print ws.Name
Next

End Sub

P.S. not sure why SO cannot recognise the code this time...

answered Sep 23, 2022 at 10:24

NoobVB

1,0337 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Visual Basic - simple 1-dimensional array with Strings giving syntax error [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related