Module Definitions

[Rocky0201]

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...

 

[pgc01]

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]

Thanks pcg01...

I have not used classes before. I tried the following wich cause a compile error "Method or data member not found".

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]

You just need to use for ex. the dictionary object.

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

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]

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...

 

[pgc01]

I'm glad it helped. Cheers!

 

[Rocky0201]

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