# A better way to substitute 2 vba lines



## hsandeep (Jan 3, 2023)

In a ThisWorkbook vba of my Workbook, I am using *a long* vba lines as


```
Dim ary As Variant, i As Long
ary = Array(Sheet86, Sheet83, Sheet82, Sheet81, Sheet80, Sheet39, Sheet36, Sheet33, Sheet42, Sheet77, Sheet78, Sheet79, Sheet99, Sheet100, Sheet101, Sheet102, Sheet103, Sheet104, Sheet111, Sheet112, Sheet113, Sheet114, Sheet115, Sheet116)
```

Is there a *better/professional way* ‘how to write the above lines keeping the code's action same?
How to accomplish?
Thanks in advance.


----------



## jolivanes (Jan 3, 2023)

Are you not missing the double quotations around the sheet names? Not that it helps in your request.


----------



## JGordon11 (Jan 3, 2023)

using a helper function


```
Function SheetList(Prefix As String, ParamArray sheetnames())
    Dim sn, spl, i As Long, t As String, stp As Long
    For Each sn In sheetnames
        If InStr(1, sn, "-") = 0 Then sn = sn & "-" & sn
        spl = Split(sn, "-")
        If --spl(1) >= --spl(0) Then
            stp = 1
        Else
            stp = -1
        End If
        For i = spl(0) To spl(1) Step stp
            t = t & Prefix & i & ","
        Next
    Next
    t = Left(t, Len(t) - 1)
    SheetList = Split(t, ",")
End Function
```

you could write you ary assignment line as


```
ary = SheetList("Sheet", 86, "83-80", 39, 36, 33, 42, "77-79", "99-104", "111-116")
```

ary will be an array of names of type string (not of sheet objects as your ary assignment is doing as jolivanes pointed out).

if you actually do want the sheet objects in the array, then the helper function would need to be modified slightly.


----------



## jolivanes (Jan 3, 2023)

You could have the numbers from low to high (aesthetically pleasing), unless there is a reason to have them the way you have it.
I doubt that this qualifies as "better/professional" but it is shorter.

```
Sub Maybe()
Dim myArr, i As Long
myArr = Array(33, 36, 39, 42, 77, 78, 79, 80, 81, 82, 83, 86, 99, 100, 101, 102, 103, 104, 111, 112, 113, 114, 115, 116)
    For i = LBound(myArr) To UBound(myArr)
        Sheets("Sheet" & shArr(i)).Cells(1, 1).Value = "Hello World."    '<---- Do whatever is needed to do here
    Next i
End Sub
```


----------



## My Aswer Is This (Jan 3, 2023)

I would do it this way.
Using select case
Modify to your needs.

```
Sub My_Answer()
Dim i As Long

For i = 1 To Sheets.Count
    With Sheets(i)
        Select Case Sheets(i).Index
            Case 1 To 2, 7 To 12, 18
                .Range("A1").Value = "Alpha"
                .Range("B1").Value = "Bravo"
        End Select
    End With
Next
MsgBox "Done"
End Sub
```


----------



## hsandeep (Jan 4, 2023)

jolivanes said:


> You could have the numbers from low to high (aesthetically pleasing), unless there is a reason to have them the way you have it.
> I doubt that this qualifies as "better/professional" but it is shorter.
> 
> ```
> ...


My code is below:

```
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Deletes data in F2:G42 IF cell A2="z" The code triggers ONLY when the file is clicked SAVED
'Then the code FILLS BLANKS in F2:G42 by removing the EXISTING VALUES if any from the worksheets
'tab named H001:H9
'tab named H31:H36
'tab named H41:H46

'Declare the variables

    Dim ary As Variant, i As Long
    ary = Array(Sheet86, Sheet83, Sheet82, Sheet81, Sheet80, Sheet39, Sheet36, Sheet33, Sheet42, Sheet77, Sheet78, Sheet79, Sheet99, Sheet100, Sheet101, Sheet102, Sheet103, Sheet104, Sheet111, Sheet112, Sheet113, Sheet114, Sheet115, Sheet116)
                                With Application
                                    .Calculation = xlCalculationManual
                                    .EnableEvents = False
                                    .ScreenUpdating = False
                                End With
            For i = 0 To UBound(ary)
                    If ary(i).Range("A2") = "z" Then ary(i).Range("F2:G42").ClearContents
            Next i
                                With Application
                                    .Calculation = xlCalculationAutomatic
                                    .EnableEvents = True
                                    .ScreenUpdating = True
                                End With
End Sub
```

*WHAT MODIFICATION IS SUGGESTED*?


----------

