vba - Use a variable with a "dynamic created name"

Question

I hope you can help me with a crazy idea. I am not sure if this is possible and I don't even know how to call this.

I would like to use a variable building its name on the fly.

Is this possible?

I am reading an html file line by line and my code needs to know in what sheet to store current line. I have around 10 sheets but for the question, I will use only two sheets: Module and Switch. For every sheet I have a "WhateverSheetRow" variable to keep record of the updated row. In this example: ModuleRow and SwitchRow. My thought is that, because I am writing in each Row of each Sheet, I know which Row is the last one updated, so in the next occurrence, I will increase it by 1 and update it instead of using find or any other "last empty Row" updated method. Is there a way to increase WhateverSheetRow by 1 building the variable name on the fly?

Dim ModuleRow as Long
Dim SwitchRow as Long
Dim Found as String
If Found = "Module" Then             'String found is Module
Found & "Row" = Found & "Row" + 1    'Increase ModuleRow by one
Sheets(Found).Range("A" & (ModuleRow)).Value = "Y"

So the question is if there is a way to "build" the variable name and use it.

Or what would be a better option?

Thanks a lot!

Can you include a visual example of what you're saying? I assume you know that you could do `moduleRow=ModuleRow +1`? — pgSystemTester, Dec 12 '21 at 07:20
If I have understood correctly your requirement, I think a public variable should solve your problem. — Charlie, Dec 12 '21 at 07:40
Read up on arrays, collections and scripting dictionaries. You may also find that working through a basic VBA tutorial may helpful as your question demonstrates significant lack of programming fundamentals. — freeflow, Dec 12 '21 at 10:33

score 0 · Answer 1 · answered Dec 12 '21 at 11:35

You can use that (bad) idea in some other languages but not in VBA. Use either an Array or a Dictionary. If you are changing the number or position of the worksheets in your script then use a Dictionary.

Option Explicit

Sub demo()

    Dim wb As Workbook, ws As Worksheet
    Set wb = ThisWorkbook
    
    ' using an array
    Dim arLastRow() As Long
    ReDim arLastRow(1 To wb.Sheets.Count)
    For Each ws In wb.Sheets
        arLastRow(ws.Index) = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Next
    
    ' set found
    Dim Found As String
    Found = "Module" ' or Switch or any other sheet name
    With wb.Sheets(Found)
       arLastRow(.Index) = arLastRow(.Index) + 1
        .Cells(arLastRow(.Index), "A") = "Y"
    End With
    
    ' or using a dictionary
    Dim dictLastRow As Object
    Set dictLastRow = CreateObject("Scripting.Dictionary")
    For Each ws In wb.Sheets
        dictLastRow(ws.Name) = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Next
       
    ' set found
    Found = "Switch" ' or Module or any other sheet name
    With wb.Sheets(Found)
        dictLastRow(.Name) = dictLastRow(.Name) + 1
        .Cells(dictLastRow(.Name), "A") = "Y"
    End With
End Sub

Thanks for the answer. I am trying to avoid .End(xlUp) because it is very slow for the amount of data I have. I've been working some ways around but it looks like I will need to look for efficiency somewhere else in my code. I will give your code a try. Thanks again for the help. — Rodrigo NAvarro, Dec 12 '21 at 15:38
@Rodgrigo The `.End(xlUp)` only runs once for each sheet at the beginning of the sub to find any existing rows, If your sheets are empty then you can replace it with the row number you want to start at. — CDP1802, Dec 12 '21 at 15:43

vba - Use a variable with a "dynamic created name"

1 Answers1