How to define dynamic variables in Excel VBA?

Question

I'm a beginner with VBA Excel and I'm trying to do an application for my thesis, that has to manipulate excel files during the process.

I will basically have a workbook composed by many worksheets, and I want to save effectively some data, using only one FOR to save some time. The problem is that I don't know which are going to be the names of the worksheets imported (already done this importation part).

So, I want to save the columns where I found some data (strings 'Create' and 'Delete'), but I can't assign them to a static variable, because they'll be overwritten. So my idea was to put them on dynamic variables, according to the name of the sheet, but I don't know how to do that.

My first try was the following, but the code doesn't work... ws.Name & c = w and ws.Name & d = w are giving error.

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet1" And ws.Name <> concepts Then
         For w = 1 To ws.Name.Cells(1, Columns.Count).End(xlToLeft).Column
             If ws.Name.Cells(1, w).Value = create Then
                 ws.Name & "c" = w
             ElseIf ws.Name.Cells(1, w).Value = delete Then
                 ws.Name & "d" = w
             End If
         Next w
    EndIf
Next

Can someone please help me?

FYI, as you have it, create and delete are variables. If you're trying to see if the value is the strings, then you need to wrap those in quotes, e.g. ... = "create", or ... = "delete". — BruceWayne
– BruceWayne, Commented Apr 24, 2019 at 14:15
Are all the values unique? If so you could use a Collection or Dictionary of values — Tom
– Tom, Commented Apr 24, 2019 at 14:18
If I'm understanding correctly, you're wondering if you can indirectly create and reference variables. i.e. in the loop, assign a value to a variable named "Name1c" (sheet name = "Name1") or a variable named "Name1d", etc for each sheet. @Tom is right. There isn't a simple solution using dynamic variables. However, it sounds like for the dynamic variables to work, you're already assuming that the sheet names are unique. From your existing code, I do believe either a dictionary or collection may be the easiest solution. — Mistella
– Mistella, Commented Apr 24, 2019 at 14:30
Also, if the dynamic variable idea had worked, with the way the code is set up, at the end of the For-loop each of the variables would only have a single value. If there is more than one create or destroy per sheet, the code would only save the w value for the last one. — Mistella
– Mistella, Commented Apr 24, 2019 at 14:33

Tom · Accepted Answer · 2019-04-24 14:38:46Z

1

Example of using a dictionary

Dim dict As Object
Dim dictKey As String

Set dict = CreateObject("Scripting.Dictionary")

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet1" And ws.Name <> concepts Then
        For w = 1 To ws.Name.Cells(1, Columns.Count).End(xlToLeft).Column
            If ws.Name.Cells(1, w).Value = Create Then
                dictKey = ws.Name & c
            ElseIf ws.Name.Cells(1, w).Value = Delete Then
                dictKey = ws.Name & d
            End If

            If Not dict.exists(dictKey) Then
                dict.Add dictKey, w
            Else
                MsgBox dictKey & " already exists"
            End If
         Next w
    End If
Next ws

answered Apr 24, 2019 at 14:38

Tom

9,9084 gold badges33 silver badges51 bronze badges

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

7 Comments

Mistella Over a year ago

Further explanation: This will only keep the first instance of each create/delete in a worksheet. If the last is desired, the existing value could be replaced with the newest found.

Mathieu Guindon Over a year ago

Tip: reference the Microsoft Scripting Runtime library, then you can declare dict As Dictionary, do Set dict = New Dictionary, and get intellisense & autocompletion as you type. Every single Windows box built this century has the same version of that library, there's no reason to systematically late-bind it.

Francisca Parente Cambra Over a year ago

@Mistella, and to get the values from the dictKeys i just have to do ´x = dict("Namec") right?

Tom Over a year ago

@FranciscaParenteCambra exactly

Francisca Parente Cambra Over a year ago

Just one more thing @Mistella, the ´If Not´ is adding the value of w to the respective dictKey, but only if the key doesn't exist in the dictionary? Is not it the other way around?

|

Collectives™ on Stack Overflow

How to define dynamic variables in Excel VBA?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related