loop through all public declared variables in a module

[stranno]

hi,
I would like to loop through all public declared variables in a certain module. Is that possible?

stranno

[Paul_Hossler]

In order to do what?

[mikerickson]

YEs it is possible, something like


Dim oneThing as Variant

For each oneThing in Array(var1, var2, var3,.....)
Rem do something
Next oneThing

Where you replace var1, var2, etc with the names of the variables in question.

[stranno]

In order to do what?

To check their value at a certain point.

I meant something like:

For each declared_public_variable in all declared variables in module1
msgbox declared_publuc_variable.value
next

[stranno]

YEs it is possible, something like


Dim oneThing as Variant

For each oneThing in Array(var1, var2, var3,.....)
Rem do something
Next oneThing

Where you replace var1, var2, etc with the names of the variables in question.

But then i have to type all variables (207 in my case) in an array. Right?

[mikerickson]

That's how post#3 would work. I'm putting something together that might make it easier.

Just a couple of questions:
--- Will a solution that ignores arrays work for you?
Arrays are going to be an issue.
Public myArray as Variant
Public myStrings() as String
Public Size as Long

Sub macro()
Dim myArray(1 to 10)
End Sub
What I have in mind will work fine for Size, but both myArray and myStrings will be issues.

--- Are all these variables identified with the "Public" keyword, i.e. no "Global" or other declaration??
--- Are all these modules in question normal modules, i.e. no sheet or other object modules??

[snb]

Ben je bekend met het hulpvenster 'Locals' in de VBEditor ?

[stranno]

That's how post#3 would work. I'm putting something together that might make it easier.

Just a couple of questions:
--- Will a solution that ignores arrays work for you?
Arrays are going to be an issue.
Public myArray as Variant
Public myStrings() as String
Public Size as Long

Sub macro()
Dim myArray(1 to 10)
End Sub
What I have in mind will work fine for Size, but both myArray and myStrings will be issues.

--- Are all these variables identified with the "Public" keyword, i.e. no "Global" or other declaration??
--- Are all these modules in question normal modules, i.e. no sheet or other object modules??

Yes they are all Public
Yes in normal Modules
Don't know what you mean by "solution that ignores arrays". I would like to loop through all the variables. So i guess an array is needed.

[mikerickson]

If those variables are an array, it can be an issue.

[snb]

I can't see how you can refer to a variable, stored by it's 'name' in an array, collection or dictionary

[stranno]

Ben je bekend met het hulpvenster 'Locals' in de VBEditor ?

Ja dat is een handig venster. Maar ik wil ze graag 'on the fly' controleren. Dus tijdens de uitvoering van een procedure.
Is het mogelijk om dit venster in te lezen in een array?

[mikerickson]

What I am working on is writing code with code.
Given a name "myVariable"
Write a function to some module
"Function foo()
foo = myVariable
End Function"
Then call foo.

That approach is not going well. At this point, it looks like (if it would work at all) in order to call the newly written function, I'd have to put it in a different workbook, save and close that, and re-open it THEN call the function. (Plus other issues)

Its not looking good.

[stranno]

Well guys, don't spend too much time on this issue. I'll try a workaround. I didn't know it was so hard to get it done.

[SamT]

In the VBA Editor Menu >>Tools >> Options >> Editor Tab, Check the check boxes in the Code Settings Frame.

Where in the code you want to see a Variables' value, set a Breakpoint. When the code breaks, hover the mouse over the variable.

You can set as many Breakpoints as you want

[mikerickson]

I've come up with a horribly messy work-around.
Insert a new module into your project and past this code into it.
Run SetUp. Save the workbook, close the workbook, re-open the workbook.
Do not run SetUp again.
At this point, given the name of a public variable, you can get its value with

MsgBox CallByName(ThisWorkbook, "variableName", VBget)

To see how it could work, run Demo, after changing "Module1" to the module of your choice.


Sub SetUp()
Dim oneComponent As Object
Dim VNameArray As Variant, oneVName As Variant
For Each oneComponent In ThisWorkbook.VBProject.VBComponents
With oneComponent
If .Type = vbext_ct_StdModule Then
VNameArray = ArrayOfPublicVariableNames(.Name)
If 0 = LBound(VNameArray) Then
For Each oneVName In VNameArray
PropertyGetCode .Name, CStr(oneVName)
Next oneVName
End If
End If
End With
Next oneComponent
End Sub

Sub Demo()
Dim VNameArray As Variant, oneVName As Variant
VNameArray = ArrayOfPublicVariableNames("Module1")
'myVariable = "cat"
'myVariable2 = "fish"
If 0 = LBound(VNameArray) Then
For Each oneVName In VNameArray
With Range("a65536").End(xlUp).Offset(1, 0)
.Cells(1, 1) = oneVName
.Cells(1, 2) = CallByName(ThisWorkbook, oneVName, VbGet)
End With
Next oneVName
End If
End Sub

Function ArrayOfPublicVariableNames(ModuleName As String) As Variant
Dim Words As Variant
Dim Pointer As Long, i As Long, j As Long
Dim Result() As String
ReDim Result(0 To 0)
Pointer = -1
With ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
For i = 1 To .CountOfDeclarationLines
Words = Split(.Lines(i, 1), " ")
For j = 0 To UBound(Words) - 1
If Words(j) = "'" Or Words(j) = "Rem" Then Exit For
If Words(j) = "Public" Then
Pointer = Pointer + 1
If UBound(Result) < Pointer Then ReDim Preserve Result(0 To 2 * Pointer)
Result(Pointer) = Words(j + 1)
End If
Next j
Next i
End With

If Pointer < 0 Then
ReDim Result(-1 To -1)
Else
ReDim Preserve Result(0 To Pointer)
ArrayOfPublicVariableNames = Result
End If
End Function

Function PropertyGetCode(ModuleName As String, VariableName As String) As String
PropertyGetCode = "Property Get " & VariableName & "() as Variant"
PropertyGetCode = PropertyGetCode & vbLf & " " & VariableName & " = " & ModuleName & "!." & VariableName
PropertyGetCode = PropertyGetCode & vbLf & "End Property" & vbLf

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.AddFromString PropertyGetCode
End With
End Function

[SamT]

At this point, given the name of a public variable, you can get its value with

MsgBox CallByName(ThisWorkbook, "variableName", VBget)



Wouldn't this do the same that by itself, given the variable name

MsgBox CStr(Variable)

I think the OP wants
Sub ShowAllInMsgBox()
Dim VNameArray As Variant, oneVName As Variant
Dim Msg As String

VNameArray = ArrayOfPublicVariableNames("Module1")
'myVariable = "cat"
'myVariable2 = "fish"
If 0 = LBound(VNameArray) Then
For Each oneVName In VNameArray
Msg = Msg & oneVName & ":= " _
& CallByName(ThisWorkbook, oneVName, VbGet) _
& vbCrLf

Next oneVName
MsgBox Msg
End If
End Sub

[snb]

As long as (s)he doesn't explain more on the objective is seems a rather senseless requirement.

[SamT]

IMO, there's something wrong with a VBA Project that has 207 public Variables in one module.

If the OP will post the Variable Declaration section of the code, I can easily turn it into an array of variables and an array of Variable Names (strings)

[stranno]

IMO, there's something wrong with a VBA Project that has 207 public Variables in one module.

If the OP will post the Variable Declaration section of the code, I can easily turn it into an array of variables and an array of Variable Names (strings)

The variables refer to the field names of 207 columns. They all look like: Public Ks_kstrv1 as single, Public Ks_dgrh2v as string .... and so on.

As Mickerickson suggested i have stored them in a variant array. If my requirement looks senseless, then consider it as a theoretical question. There is nothing wrong with that.

[snb]

What is so theoretical about this ? No theory without a question. And theory shouldn't be senseless at all, unless any question is lacking.

[stranno]

What is so theoretical about this ? No theory without a question. And theory shouldn't be senseless at all, unless any question is lacking.

snb,
you asked for a reason (a practical application). I suggested to consider it as an hypothetical or theoretical question. Why this semantic discussion?

this is what i did and it worked for me:

Public Ks_qwerty As String
Public Ks_ytrewq As String
Public Ks_number1 As Integer

Sub n()
Dim arr As Variant
Ks_qwerty = "hello"
Ks_ytrewq = "olleh"
Ks_number1 = 4
arr = Array(Ks_qwerty, Ks_ytrewq, Ks_number1)
For i = 0 To UBound(arr)
MsgBox arr(i)
Next
End Sub

Thanks mikerickson

[snb]

Please check the meaning of 'semantics' before using it incorrectly.

[stranno]

snb,
You react rather hostile. Why? For you:

Semantics (from Ancient Greek (https://en.wikipedia.org/wiki/Ancient_Greek): σημαντικός (https://en.wiktionary.org/wiki/%CF%83%CE%B7%CE%BC%CE%B1%CE%BD%CF%84%CE%B9%CE%BA%CF%8C%CF%82) sēmantikós, "significant")[1] (https://en.wikipedia.org/wiki/Semantics#cite_note-1)[2] (https://en.wikipedia.org/wiki/Semantics#cite_note-2) is the study of meaning (https://en.wikipedia.org/wiki/Meaning_(linguistics)). It focuses on the relation between signifiers, like words (https://en.wikipedia.org/wiki/Word), phrases (https://en.wikipedia.org/wiki/Phrase), signs (https://en.wikipedia.org/wiki/Sign), and symbols (https://en.wikipedia.org/wiki/Symbol), and what they stand for; their denotation (https://en.wikipedia.org/wiki/Denotation). Linguistic semantics is the study of meaning that is used for understanding human expression through language. Other forms of semantics include the semantics of programming languages, formal logics, and semiotics (https://en.wikipedia.org/wiki/Semiotics). In international scientific vocabulary (https://en.wikipedia.org/wiki/International_scientific_vocabulary) semantics is also called semasiology (https://en.wikipedia.org/wiki/Semasiology).
The word semantics itself denotes a range of ideas—from the popular to the highly technical. It is often used in ordinary language for denoting a problem of understanding that comes down to word selection or connotation (https://en.wikipedia.org/wiki/Connotation). This problem of understanding has been the subject of many formal enquiries, over a long period of time, especially in the field of formal semantics (https://en.wikipedia.org/wiki/Formal_semantics_(linguistics)). In linguistics (https://en.wikipedia.org/wiki/Linguistics), it is the study of the interpretation of signs or symbols used in agents (https://en.wikipedia.org/wiki/Agent_(grammar)) or communities (https://en.wikipedia.org/wiki/Community) within particular circumstances and contexts.[3] (https://en.wikipedia.org/wiki/Semantics#cite_note-Neurath1955-3) Within this view, sounds, facial expressions, body language, and proxemics (https://en.wikipedia.org/wiki/Proxemics) have semantic (meaningful) content, and each comprises several branches of study. In written language, things like paragraph structure and punctuation bear semantic content; other forms of language bear other semantic content.[3] (https://en.wikipedia.org/wiki/Semantics#cite_note-Neurath1955-3)
The formal study of semantics intersects with many other fields of inquiry, including lexicology (https://en.wikipedia.org/wiki/Lexicology), syntax (https://en.wikipedia.org/wiki/Syntax), pragmatics (https://en.wikipedia.org/wiki/Pragmatics), etymology (https://en.wikipedia.org/wiki/Etymology) and others. Independently, semantics is also a well-defined field in its own right, often with synthetic properties.[4] (https://en.wikipedia.org/wiki/Semantics#cite_note-4) In the philosophy of language (https://en.wikipedia.org/wiki/Philosophy_of_language), semantics and reference (https://en.wikipedia.org/wiki/Reference) are closely connected. Further related fields include philology (https://en.wikipedia.org/wiki/Philology), communication (https://en.wikipedia.org/wiki/Communication), and semiotics (https://en.wikipedia.org/wiki/Semiotics). The formal study of semantics can therefore be manifold and complex.
Semantics contrasts with syntax (https://en.wikipedia.org/wiki/Syntax), the study of the combinatorics of units of a language (without reference to their meaning), and pragmatics (https://en.wikipedia.org/wiki/Pragmatics), the study of the relationships between the symbols of a language, their meaning, and the users of the language.[5] (https://en.wikipedia.org/wiki/Semantics#cite_note-5) Semantics as a field of study also has significant ties to various representational theories of meaning including truth theories of meaning, coherence theories of meaning, and correspondence theories of meaning. Each of these is related to the general philosophical study of reality and the representation of meaning.

And this is exactly what i meant with it.

Laten we elkaar niet op woorden vangen!

[SamT]

The variables refer to the field names of 207 columns. They all look like: Public Ks_kstrv1 as single, Public Ks_dgrh2v as string .... and so on.

As Mickerickson suggested i have stored them in a variant array. If my requirement looks senseless, then consider it as a theoretical question. There is nothing wrong with that.

There is nothing wrong with needing to see the values of variables at certain times, in fact it is an industry wide practice. I myself, have felt that need almost every time I write something new.

There is something wrong with a small project that has 207 Public Variables.

[Paul_Hossler]

The variables refer to the field names of 207 columns. They all look like: Public Ks_kstrv1 as single, Public Ks_dgrh2v as string .... and so on.

Can you expand on that?

If the 207 refer to field names or to a column number or to something in the column there might be easier ways

[Aflatoon]

You could put the variables into a class and then use CallByName.

[stranno]

There is nothing wrong with needing to see the values of variables at certain times, in fact it is an industry wide practice. I myself, have felt that need almost every time I write something new.

There is something wrong with a small project that has 207 Public Variables.

I have never said that it was a small project.

[stranno]

You could put the variables into a class and then use CallByName.

Yes may be a good idea.

[SamT]

It is not my job to convince you of anything.If you are convinced that public variables is the best way, that is what I have tried to help you use.

I showed you how to convert Mike's code to show all Variable Values in one MsgBox, I offered to put them in an array for you.

You have made no comments about either of those offerings,

Good luck with your project.

[stranno]

Hello SamT,

Sorry for my late response. I I had a healthproblem and wasn't able to read the posts for a while. That can happen doesn'tit?
Of course i appreciate your help. But i can't get it running. Does Mike'scode need a reference to a specific library? I get a error in the followingline:
"For Each one Component In ThisWorkbook.VBProject.VBComponents"
Now something completely different. I noticed (not for thefirst time) that a few people over here have very short wicks. Personally i amnot sitting
all day in front of my computer. So in some cases i can’t respondinstantly. Must i feel guilty about that?

Stranno

[Aflatoon]

Your error may be due to security settings. Click File - Options, Trust Center, Trust Center Settings..., and in the Macro Settings section, ensure you have the 'Trust access to the VBA project object model' checked. Otherwise your code can't do anything that uses the VBProject object.

[nishatian1]

Your problem is a bit complicated to understand

[nishatian1]

Could you please share more information on this?

[nishatian1]

I will try my best to solve it and share the solution with you :)

[SamT]

You need a reference to Microsoft Visual Basic For Applications Extensibility.

Just for Clarity, these are the subs in Mike's offering that do the work. Sub Demo is a demonstration of after these subs have added Property Get Procedures to the ThisWorkbook Code module. Once Sub Setup is run, you can use CallByName as Mike did in his first code snippet.

This Function is also required to run Demo.
Function ArrayOfPublicVariableNames(ModuleName As String) As Variant
'Gets all Public Variables and Constants from Standard Module named Modulename

Dim Words As Variant
Dim Pointer As Long, i As Long, j As Long
Dim Result() As String
ReDim Result(0 To 0)
Pointer = -1
With ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
For i = 1 To .CountOfDeclarationLines
Words = Split(.Lines(i, 1), " ")
For j = 0 To UBound(Words) - 1
If Words(j) = "'" Or Words(j) = "Rem" Then Exit For
If Words(j) = "Public" Then
Pointer = Pointer + 1
If UBound(Result) < Pointer Then ReDim Preserve Result(0 To 2 * Pointer)
Result(Pointer) = Words(j + 1)
End If
Next j
Next i
End With

If Pointer < 0 Then
ReDim Result(-1 To -1)
Else
ReDim Preserve Result(0 To Pointer)
ArrayOfPublicVariableNames = Result
End If
End Function
After Running Setup, you won't need the next two

Sub SetUp()
'Loops through every Standard Module in this Workbook

Dim oneComponent As Object
Dim VNameArray As Variant, oneVName As Variant
For Each oneComponent In ThisWorkbook.VBProject.VBComponents
With oneComponent
If .Type = vbext_ct_StdModule Then
VNameArray = ArrayOfPublicVariableNames(.Name)
If 0 = LBound(VNameArray) Then
For Each oneVName In VNameArray
PropertyGetCode .Name, CStr(oneVName)
Next oneVName
End If
End If
End With
Next oneComponent
End Sub


Function PropertyGetCode(ModuleName As String, VariableName As String) As String
'Writes Property Get Procedure to the ThisWorkbook Code Page

PropertyGetCode = "Property Get " & VariableName & "() as Variant"
PropertyGetCode = PropertyGetCode & vbLf & " " & VariableName & " = " & ModuleName & "!." & VariableName
PropertyGetCode = PropertyGetCode & vbLf & "End Property" & vbLf

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.AddFromString PropertyGetCode
End With
End Function

[snb]

Sub M_snb()
With ThisWorkbook.VBProject.VBComponents("Modul1").CodeModule
msgbox Join(Filter(Split(.Lines(1, .CountOfDeclarationLines), vbCr), "Public "), vbLf)
End With
End Sub

[stranno]

Guys, you are the best. I am very pleased with this solution. Two Thumbs up. I tried mike's solution at my office but indeed the trust center settings were inadequate.
Now it works fine. Just what i needed.

Stranno

[stranno]

snb your solution is also superb! So compact!

[snb]

Sub M_snb()
For Each it In ThisWorkbook.VBProject.VBComponents
If it.Type = 1 Then MsgBox Join(Filter(Split(it.codemodule.Lines(1, it.codemodule.countofdeclarationlines), vbCrLf), "Public "), vbLf)
Next
End Sub