CODEWITHSUNDEEP
excelvba
Francisca Parente Cambra
Francisca Parente Cambra

Reputation: 25

How to define dynamic variables in Excel VBA?

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?

Upvotes: 1

Views: 2015

Answers (1)

Tom
Tom

Reputation: 9878

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

Upvotes: 1

Related Questions