Module Definitions

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
Hi.

I have many definitions in my Module2. They are various as in Array's and Constants, etc.

There are a few that I have defined as Longs. Example:

Public MSL as Long
Public MSP as Long
.
.
etc
.

Can I at runtime run through the list of definitions and when I come to a match, insert a value to that definition?

Say Cell A1 in Sheet1 = MSL

Example:
For I = 1 To end of definitions in Module2
If Sheets("Sheet1").cells(1,1) = List in Module2 then
List in Module 2 = 1
Endif
Next

Perhaps this is not possible at runtime?

Thanks...
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

No, that is not possible. You can, however, use a Dictionary or a Collection object. This objects can associate strings with values, which seems to be what you need. In this case you can change them at runtime with no problem.
 

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
Thanks pcg01...

I have not used classes before. I tried the following wich cause a compile error "Method or data member not found".
Code:
Dim TabNames as Class1
 
ReDim SN(ThisWorkbook.Sheets.Count, 0)
ReDim TabAbbrev(2)
 
For I = 1 To ThisWorkbook.Sheets.Count
[INDENT]For ii = TabStartColumn To Sheets("VBA Definitions").Cells(1, Columns.Count).End(xlToLeft).Column
[/INDENT][INDENT][INDENT]TabAbbrev = Split(Sheets("VBA Definitions").Cells(1, ii), "-")
[/INDENT][/INDENT][INDENT][INDENT]TabNames.Add Item:=TabAbbrev(1), Key:=CStr(Num)
[/INDENT][/INDENT][INDENT][INDENT]If ThisWorkbook.Sheets.Item(I).Name = TabAbbrev(0) Then
[/INDENT][/INDENT][INDENT][INDENT][INDENT]SN(ThisWorkbook.Sheets.Item(I).Index, 0) = ThisWorkbook.Sheets.Item(I).Name
[/INDENT][/INDENT][/INDENT][INDENT][INDENT]End If
[/INDENT][/INDENT][INDENT]Next ii
[/INDENT]Next
I am not certain how to code the parameters for using classes.

The VBA help is not making sense to me.

Thanks again pcg01
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
You just need to use for ex. the dictionary object.

This is an example. Insert a new module, paste the code and execute Test():

Code:
Option Explicit
 
Public dic As Object
 
Sub Init()
 
Set dic = CreateObject("Scripting.Dictionary")
' dic.CompareMode = vbTextCompare ' if you want case insensitive
 
' Initialize the dictionary with the string you want, with a default value of 0
dic.Add "MSL", 0
dic.Add "MSM", 0
dic.Add "MSN", 0
dic.Add "MSO", 0
dic.Add "MSP", 0
End Sub
 
' Set A1 to the string "MSL" and A2 to "MSP" (without the quotes) and execute Test()
Sub Test()
 
' initialise the dictionary
Init
 
' Set the value of the keys in A1 and A2 to 2
If dic.exists(Range("A1").Value) Then _
    dic.Item(Range("A1").Value) = 2
If dic.exists(Range("A2").Value) Then _
    dic.Item(Range("A2").Value) = 2
 
' Display the values in the dictionary
MsgBox _
    "MSL: " & dic("MSL") & ", " & vbNewLine & _
    "MSM: " & dic("MSM") & ", " & vbNewLine & _
    "MSN: " & dic("MSN") & ", " & vbNewLine & _
    "MSO: " & dic("MSO") & ", " & vbNewLine & _
    "MSP: " & dic("MSP")
 
End Sub
 

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278

ADVERTISEMENT

Thank you PGC...

I do believe this is exactly the process I need. I inserted your example into my code and seems to work great...
 

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
PGC... I'm not certain if I can continue this thread or start a new thread.

Anyhow, here is my question.

I am able to successfully create all my references using the dic.add process. This works wonderful.

Prior to creating my references, I build several multi dimensional arrays that is associated with each reference. Exapmle: MSLTRec is a variant array, MSLFormat is a variant array, etc. These two as well as others are associated with my dic reference "MSL".

Is it possible to pass to a subroutine my reference dic("MSL);

Call MySub(dic("MSL"))

and in my subroutine use the array's associated to MSL?

When I create my dictionary reference MSL, I tried to "connect" my arrays to MSL but have been unsuccessful.

dic.Add "MSL", 0
dic("MSL") = MSLTRec

Hope this is not such an odd question.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,442
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top