Solved: A variable within a variable (within a variable?)

[Stargazer]

Hiya,

Not been back here in a while as my focus moved away from VBA for a short while, but we've picked up another project that needs to be done in Excel and in an attempt to streamline an ugly and unpleasant module, we're dynamicising but have stumbled into a problem.

I'm not even sure it's 100% possible, but if it is, someone here will know how to do it.

We have a series of worksheets named Sun, Mon, Tue, etc, etc... These sheets are referenced dynamically with a 'For Each'. We then need to assign a variable value to the worksheet that, is itself, a variable... For example...



Dim SunOrders, MonOrders, TueOrders, etc, etc

For Each Sheet in Thisworkbook.Sheets
If Len(Sheet.Name) = 3 Then
WShtVar = Sheet.Name & "Orders"

'This following bit is where we need help.
'I shall put in what we 'think' it should look like
'and also to give you an idea about where we
'are approaching from'

WShtVar.Value = Sheets(Sheet.Name).Cells("Blah, blah")
End If
Next

So, to sum up, the WShtVar variable, once defined, needs to then have a value assigned to it so we can use it correctly.

If anyone knows a way this can be done, we would be eternally grateful to hear your thoughts. Conversely, if this is perhaps a bit ambitious, feel free to hit me with a large dead fish and then point an laugh.

Thanks in advance,

Rob.

[Bob Phillips]

Not sure I have got it, but wouldn't you just do Copy ... Paste?

[Stargazer]

Copy and paste?

This is about actually assigning a variable parameter to something that has already been declared as a variable.

So we say that Variable = Sheet.Name & "Orders". For arguments sake we will assume that the variable, quite appropriately named 'Variable' now has a value of MonOrders.

What we then need to do is find a clever and ingenious way to give Variable a dynamic value for MonOrders. So let's pretend we havn't used Variable yet for the sheet name. The next line of code might read MonOrders = "Something".

But because we've found MonOrders dynamically, we then need to apply a dynamic value to the original dynamic.

Now we can't say Variable = "Something" 'after' saying Variable = Sheet.Name & Orders because all that will do is change the value assigned to the tag 'Variable'. There's no point doing it before for obvious reasons so that leaves us with the conundrum of being able to give 'Variable' a value to its value.

So 'Variable' will equal MonOrders, but underneath the variable value of MonOrders, we need it to know that MonOrders equals "Something".

This would really open up a whole range of possibilites if we could get this working so would be willing to hear any ideas, no matter how zany.

Cheers,

Rob.

[Bob Phillips]

I am still not sure I get it, but my reading is that you work out some value dynamically, then you want to create a new variable by that name. In short, you cannot dynamically create variables, they have to be declared.

But ... maybe this will work for you, a collection



Dim collRob As Collection
Dim someVar As String

Set collRob = New Collection

someVar = ActiveSheet.Name & "Orders"
collRob.Add 27, someVar
MsgBox collRob(someVar)

[Aflatoon]

Or you might use an array (perhaps using day number as the index) or a Dictionary.

[Stargazer]

@xld

I've just run your code in a test workbook. I'm using a 'For Each' to go through the worksheets and someVar is picking up its value correctly.

However, I'm not sure what this collection thing is. When the Msgbox displays, it simply says "27" for all of the four worksheets I've created.

I've never used a 'collection' before so will have to research them a bit to see if it's the way to go.

@Aflatoon

Arrays and Dictionarys. Again, not something I've ever called on in VBA before so I'll do some research and see if I can see some potential.

Cheers,

Rob.

[mikerickson]

Have it so each sheet's code module begins with the line
Public myVariable As String
Then code like this can be used.
Sheet1.myVariable = "one"
ThisWorkbook.Sheets("Sheet2").myVariable = "two"
MsgBox Sheets(1).myVariable & vbCr & Sheets(2).myVariable

[Bob Phillips]

@xld

I've just run your code in a test workbook. I'm using a 'For Each' to go through the worksheets and someVar is picking up its value correctly.

However, I'm not sure what this collection thing is. When the Msgbox displays, it simply says "27" for all of the four worksheets I've created.

In my example, I loaded the collection item with a value of 27, just to demonstrate it. You would replace the 27 with your values.


've never used a 'collection' before so will have to research them a bit to see if it's the way to go.

That was the idea, I give you a suggestion, you check it out.

[Paul_Hossler]

My 2 cents -- looks to me like further analysis of the real issue might be helpful. I don't think you can take full advanatge of Excel and VBA using that approach


'Option Explicit is your friend
Option Explicit

Sub One()

'I like to explicitly Dim everything
Dim SunOrders As String, MonOrders As String, TueOrders As String
Dim ws As Worksheet
Dim WShtVar As String

For Each ws In ThisWorkbook.Sheets
If Len(ws.Name) = 3 Then

'this imples that WShtvar is a string
WShtVar = ws.Name & "Orders"

'Strings do not have .value
Worksheets(WShtVar).Range("A1").Value = ws.Cells("B2").Value

End If
Next

End Sub





Another way that set object variables


Option Explicit

Public wsDaily As Worksheet

Sub SetDaily()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
If Len(ws.Name) = 3 Then
Set wsDaily = Worksheets(ws.Name & "Orders")
End If
Next
End Sub

Sub UseDaily()
wsDaily.Range("C3") = 1231234
' etc, etc,
End Sub




Paul

[Norie]

Why not use an array?

Something like this perhaps.


Dim Orders(1 To 7)
Dim arrDays
Dim lngDay As Long
arrDays = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
For Each sh In ThisWorkbook.Sheets
If Len(sh.Name) = 3 Then
lngDay = Application.Match(sh.Name, arrDays, 0)
Orders(lngDay) = sh.Cells(10, 34)
End If
Next


This should put the values in the array Orders.

The value from the Sun sheet will go in Orders(1), Mon in Orders(2) and so on.

[Stargazer]

We have a winner!

Many thanks everyone for all the help and advice. After some poking and prodding to make it fit our project, it turns out that Norie's array was the answer we were looking for. Take a prize from the top shelf.

Not to say that the other suggestions were not useful though. There are some good ideas that we need to investigate further to fully understand them before we start meddling and making things worse.

Many thanks again.

Rob.

[Bob Phillips]

:think:

[Stargazer]

Oh, don't get me wrong... The Collection method was good, but a combination of us making a pigs-ear of adapting it initially and then once we sorted it out, we found that it could be affected by the order in which Excel steps through the worksheets...

The Array seems to work in such a way that it allows us to remove certain caveats and still maintains a specific way of operating.

[Bob Phillips]

I just didn't see how the array meets your need as originally expressed (although I do see that I may not still have grasped what you are doing). It could be improved upon and get closer by using a 2D array, but then the matching gets harder, but it is still a long way from a collection or dictionary solution in terms of what you stated, or even a custom class.