0

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

BigBen
  • 46,229
  • 7
  • 24
  • 40
Electra
  • 101
  • 6
  • 1
    try `Dim sheets_names_array As String` new row `sheets_names_array = array("Sheet1", "Sheet2")` – NoobVB Sep 23 '22 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 Sep 23 '22 at 10:10
  • You should share a bit more code in question, as `Dim sheets_names_array() As Variant` is also possible declaration – NoobVB Sep 23 '22 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 Sep 23 '22 at 10:24
  • also, read a bit about LBound and UBound, - these functions are very commonly used while looping through arrays. – NoobVB Sep 23 '22 at 10:39
  • VBA (Visual Basic for Applications) is not the same as VB.Net. See https://en.wikipedia.org/wiki/Visual_Basic – BigBen Sep 23 '22 at 14:24

1 Answers1

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...

NoobVB
  • 989
  • 6
  • 10