Set variable values across module|VBA & Macros|Excel Forum|My Online Training Hub

Hello Pieter Cox
I suppose the answer to your first question …. I have a feeling that I need to create a Class module to achieve this but would greatly appreciate confirmation that this is the right way to go and a quick explanation of how I can do this…. .. could be Yes , and that might be what Veloria was hinting at when she said … I'd suggest a module with functions (or property procedures) that return the relevant values… , maybe she was on about something else, I dont know.

I suppose in a way it seems like you want something hard coded in a module that you can copy and paste into other workbooks that will result in a particular “global” type variable being available in that workbook that you copy the coding to
That is what I have understood from what you have said.

Your own suggestion in your Post # 5 is as good as any I suppose, but like Catalin Bombea, I am not sure if it would be the best. Perhaps you need to just try the different ways out.

Let me see if I can have a go at getting you started with a couple of ways to try

_ The Class way you originally asked about ( simplest way+++ )..
…and a quick explanation of how I can do this…
I have done a class thing about twice in my life I think, but let me see if I can get you started.. maybe someone smarter will correct me if I do it not quite right

Lets start with you wanting to do what you asked for at the start..
Set rgSampletext = Sheet1.Range("a1")

This will mean that you will need to make a class module, ( which you will need to copy to all your workbooks – a few ways there are to do that, but lets leave that one for now )
This is a Class code module that I have named Globies

Public myRange1 As Range ' because this is public, you can access it in any macro via an object made using this "blue print class thing" called Globies
Private Sub Class_Initialize() ' This is a code that springs into life when I make an object using this "blue print class thing" called Globies
Set myRange1 = Sheet1.Range("a1")
End Sub[/size]

In every macro you wanted to use this you would need to do it like this

Sub TestMeGlobyFirstCell()
Dim MyVariables As Globies: Set MyVariables = New Globies ' These two lines make an object from the blue print class thing called Globies

Dim rgSampletext As Range
Set rgSampletext = MyVariables.myRange1
'
'
'
Let rgSampletext.Value = "This should be in the first cell in Sheet1"
End Sub[/size]

+++That is the simplest way to do it. Doing it fully with class stuff gets a bit much to explain here – I think one of the main objectives of class things is to confuse people, so to understand them fully you would need a lot of spare time.

In this class way you still have to do some Setting , so you have not gained much.

_ Insensibly using Code window as a spreadsheet way
Contrary to a lot of literature and Blog sites, you don’t need to make the extra reference to a library to get at text in a code module. So you can pseudo like write your variable value in a code module, ( pseudo like Setting it outside a procedure ) and refer to it from any code module in a workbook. This way comes close to you being able to do what you wanted initially ( which gave you the "Invalid outside procedure" error ) which was to set the range at the top of the module. Its not actually technically doing that, but effectively it does the same. ( Its possibly better to say that you can effectively do the same as , I suppose )

At the top of a module ( named Module1 ) you put, for example,  the following ' comment line

' Sheet1!A1

That is effectively all that you have to do.

Then in any module in the workbook containing that, ( including any class object module), you can access that "pseudo Set already global variable" like this

Sub CodeWindowGlobyNoSetting()
Let Application.Range(Mid(ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.Lines(Startline:=1, Count:=1), 3)).Value = "This should be in the first cell in Sheet1 got from Insensible Code Module way, no Setting"
End Sub

This way does away with you having to do any setting

You might want to expand on the idea to make a function to get at the text string to make the thing a bit neater, but its probably still a bit of a wild idea.
There could also be some clever possibilities to make a very neat solution based on incorporating it into the little discovery reoported here - https://www.myonlinetraininghub.com/excel-functions-that-return-references#comment-84892 , but that’s getting even wilder.. and that might lend itself better to a more simple or sensible solution involving a hard copy spreadsheet or similar list of your global values***

But it might give you some thoughts and insights into another possibility. ( Coding example of this Insensibly using Code window as a spreadsheet way is also in the uploaded file )

Alan

P.S: ***My 2 cents is that I don’t like global variables of any sorts. I keep having problems with them. I think I would try to keep a hard coded list to copy and reference. Using the code window as I have shown is one way to do it I suppose, but probably using a worksheet is more sensible. Or you could have a text file kept in the same folder as your workbooks , and then always get the information from that. That is a nice way if all your workbooks are in the same folder…

_._____

Hello Pieter
I am rather sceptical myself about Class things. I think that, at least to some extent, they are a case of mystery for mystery’s sake
Some people smarter than me in computing tell me that there is little that can be done with Class things in VBA that can’t be done without them.
My own ( limited so far ) experience with them is that they serve two purposes at the same time:
_ They can help you organise and later understand what you did
_ At the same time they can make it more difficult for anyone else to work through your coding.
So, its helpful if you want to make it more difficult for anyone to figure out what’s going on, ( and maybe using class modules can sometime make you seem more clever than you are , Lol.. )
But that’s just my opinion based on limited experience so far…

Thanks for the feedback
Let me know if you need more clarification.

Alan

_.___________________________________________________________

Edit P.S. there were a couple of typos on those Class code snippets which I don’t seem to have a way to edit.
Here they are again.

Class module: Globies

Option Explicit
Public myRange1 As Range ' Because this is Public, you can access it in any macro via an object made using this "blue print class thing" called Globies. But Note: This one here that you see is never the one you use. Class module coding is like the blue print from which the actual coding that is used is made. We can't actually see that "real" coding

Private Sub Class_Initialize() ' This is a code that springs into life when I make an object using this "blue print class thing" called Globies. But note again: This is not the actual macro. The macro we finally have, the "real one" is a copy of this one. It appears in a code module that is a copy of this, and has the name MyVariables which I chose arbritrarily. We don't have direct access to the final made object code module

Set myRange1 = Sheet1.Range("a1") ' This works in the usual way in the final made "real" copy module. Its done at the time of that copy  being made: It sets the global variable for the real made object code module up at the top of the module. There's nothing new in that. The new thing is that the variable , myRange1 ,  becomes a property of the object which i later make from this Class blue print. That objcet, arbritrarily called  MyVariables  is made in the next code snippet

End Sub

Any normal or object code module

Option Explicit
Sub TestMeGlobyFirstCell()
Dim MyVariables As Globies ' This and the next line make an object from the blue print class thing called Globies
Set MyVariables = New Globies ' We sometimes refer to this as instantiating

Dim rgSampletext As Range
Set rgSampletext = MyVariables.myRange1 ' myRange1 is a property of the object MyVariables You can see that as you type it, because you get intellisense: http://i.imgur.com/c1YMps6.jpg
'
'
'
Let rgSampletext.Value = "This should be in the first cell in Sheet1 got from Class Way"
End Sub

I have expanded those  original  'comments above. That may or may not help: - It is sometimes difficult with these things to know how much to say. If you say too little its sometimes no use at all, at least no use in trying to help someone to learn. On the other hand, say too much and it’s just too overwhelming

The main point in my extended 'comments there were this:
The coding in the class module is the blue print of the coding you get. When you instantiate ( make ) an object from this coding, then you end up with a “real” object code module copy. But you cant see it.

To help explain what’s going on, You can think of it approximately like this ( It may not be 100% perfect technically) :
You probably know that you can look inside a worksheet object code module. ( For example, double click on it in the VB Editor to open it : http://i.imgur.com/9iTEib0.jpg ) - That is a “real” object code module . Its made by Microsoft using a Class module that we can’t see and one we cant get direct access to it.

With our home made Class module it’s the other way around. We can see the class module. But the “real one” or “real ones” that gets made, we can’t see. ( I am not sure why that is. Maybe Microsoft just wanted it that way )

I think a lot of the documentation and Blog sites don’t have it quite correct. They often refer to the ThisWorkbook and the worksheet code modules as class modules. I don’t think they are. They are object modules made form class modules we don’t have direct access to.

I think that is true what I just said. But I came up with it myself. So I welcome anyone more clued up to comment on what I have written..

_.___________________________________________________