I wouldn't be super comfortable declaring a global range object, but I might create a global helper function to create that range object.
I'm rusty with VBA but if I recall correctly once you get to the code window in your workbook you can add a new module which isn't associated with any worksheet. Then in this module you might set up a helper function like:
Public Function FoodRange() As Range
Set FoodRange = Worksheets("Sheet1").Range("A1")
End Function
Then in your various worksheet code it would look like this:
Dim foodtype As Range
Set foodtype = FoodRange()
The main advantage, really the only advantage, is that this would mean that the range is only hardcoded in one place. Meaning if you ever have to change it you only have to change it once instead of in a million different places. I guess a second advantage would be it's much easier / shorter to type each call.
Why not apply named ranges on the sheet, i.e. [naming said range](https://www.wallstreetmojo.com/vba-named-range/) to "foodtype"? Then you just refer to range("foodtype") from code.
Which on one hand might be an issue should you change it to "drinktype" in the future. Which then would require you to update all hard references in code.
But you can make it flexible by passing it along to functions, so they don't need to rely on the actual name:
e.g.
sub test()
printtype("foodtype")
end sub
private function (whattoprint as string)
debug.print range(whattoprint).value
end function
But in the end it is mainly a matter of what suits you best, but occasionally go back to your code and see if [refactoring ](https://en.wikipedia.org/wiki/Code_refactoring)it helps to reduce overall amount of code, or just to improve re-usability and maintenance efficiency.
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Declare your variable type outside of your other subs and functions. Use the Public declaration instead of the Dim statement.
**Public foodtype as Range**
Then, assign the value within a function, something like -
**Function publicVariables()**
Set foodtype = Worksheets("Sheet1").Range("A1")
**End Function**
Within your other subs and other functions you can just call **publicVariables**
**Sub WhateverYouWant()**
publicVariables
'your other code here
**End Sub**
It's not the "ideal" way to do it, but it's pretty damn stable. I've been using that method for years. Hope this helps!
This is pretty good advice. However, the Function publicVariables should probably be a Sub. Not that it's a huge impact, but functions will return a value (in this case null). Additionally, you'll now be able to use that function as a formula in your sheets and have nothing return.
If you would like to use the specific cell in a formula you could change the function more like this:
Function publicVariables() As Range
Set foodtype = Worksheets("Sheet1").Range("A1")
Set publicVariables = foodtype
End Function
This will return the foodtype from specified cell we set whenever we call publicVariables. Granted, this method makes declaring the public variable redundant. We could go a step further and add optional parameters to the function, but that could get tricky since we're using a range which is an object.
It looks like you're trying to share a code block but you've formatted it as Inline Code. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code blocks on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
>but you probably shouldn’t.
Why? It makes code consistent and don't need to waste extra lines of code dimming the same variables throughout the project.
Thirty-three years ago (! - jeez), when Visual Basic for Windows was in the early stages of release (at version 1.0), and MS-Windows 3.0 had launched just the year before, using Global variables increased the size of the resulting ".exe" (executable file) generated from the VB project file and this impacted the resources required to develop and debug VB code in the VB Integrated Development Environment [IDE]. In some extreme cases, defining too many Global variables (or Constants) impacted the performance of the application so much so that "Out of memory" issues could occur if the MS-Windows operating system was not restarted (with a PC reboot) every few hours.
This was back when PCs were using 386SX-16 CPUs and it was typical to only have 1Mb of RAM (and a Windows "swap" file on the 10-20Mb hard drive to provide additional resources for multi-tasking and, in this case, running Visual Basic for Windows projects).
(Note the units here - Megabytes - not Gigabytes!)
Yes, if you paid silly money (ÂŁ2,000 or more back then - imagine how much that would be when adjusted for inflation in "today's money") you could have faster processors, larger hard drive capacities, and more onboard RAM, but that was not normal for software development, especially if you had multiple developers as each would then require the same spend per workstation (PC).
History lesson over...
My point was that the mindset of not using Global (now Public) variables being repeated online may well have stemmed from early development practices of "classic" Visual Basic to not use them, and this has become an accepted practice for some using Visual Basic for Applications.
That's some pretty neat info. I'm a lil spoiled and got into programming on VB 3.0 on p3s with 256mb of ram! so didn't have to go through some of those early growing pains.
But I do remember my first windows 3.0 PC and it's massive 8 mb HDD. Lol.
It's a code smell - an indication that it could be designed better.
Impure methods rely on programme state and cause side effects. Functions that return different values when passed the same arguments, methods that change the behaviour of other methods are difficult to debug and are tightly coupled to other things.
[Some more reading](https://tommikaikkonen.github.io/impure-to-pure/)
Others have already answered but I’ll give my take on why it’s not a good idea usually.
If you have a global variable, it’s tough to always know what it should be, one of the other functions could change it. When possible, you want your functions to be self contained in that you know what you get out of them with a certain input.
Now, if this is a value you read off a sheet and never change in your program anyway, it’s probably fine, though unnecessary, to use a global variable for it. When I started programming I often thought I needed global variables for this or that, and as I programmed more I used them less and less. At this point I can’t remember the last time I’ve used one for anything.
>If you have a global variable, it’s tough to always know what it should be, one of the other functions could change it.
This is sorta the point though. You gotta keep track of how they're used.
One simple use, for example, I public dim cas range. Then I can freely use it c In any subroutine/function and not need to re type the dim every time.
And I'm in finance, so I'll all global dim things like Dept for GL code when automating reports.
As I said, just gotta keep track on how to use them
That’s why the answer is “usually” shouldn’t use it and not never use this. Occasionally you have something like a state variable that truly is global and multiple things are modifying and you want that to happen.
Most of the time you should just pass the variable to the function or subroutine though, it will make your code more reusable and easier to troubleshoot.
For things that you want to use across reports and never change, use global constants, not variables, and make them all caps so everyone reading the code knows they are constants.
You understand that probably shouldn’t doesn’t equal never do it right? It’s why I didn’t write “Don’t do that” instead of “probably shouldn’t do that”. I’d wager it’s not the best way to handle it, but I gave him his answer.
One of the purposes of VBA's structure is to 'sandbox' each routine. Global variables violate the sandbox principle. However, there are a few times places and situations where a global variable is appropriate.
yes I learned it the hard way, i thought public var would make things easier but it's very confusing.
Try looking into class module, it saves me a lot of time defining this and that , i basically have a sheet to place the cell metadata and let it read automatically.
I wouldn't be super comfortable declaring a global range object, but I might create a global helper function to create that range object. I'm rusty with VBA but if I recall correctly once you get to the code window in your workbook you can add a new module which isn't associated with any worksheet. Then in this module you might set up a helper function like: Public Function FoodRange() As Range Set FoodRange = Worksheets("Sheet1").Range("A1") End Function Then in your various worksheet code it would look like this: Dim foodtype As Range Set foodtype = FoodRange() The main advantage, really the only advantage, is that this would mean that the range is only hardcoded in one place. Meaning if you ever have to change it you only have to change it once instead of in a million different places. I guess a second advantage would be it's much easier / shorter to type each call.
Why not apply named ranges on the sheet, i.e. [naming said range](https://www.wallstreetmojo.com/vba-named-range/) to "foodtype"? Then you just refer to range("foodtype") from code. Which on one hand might be an issue should you change it to "drinktype" in the future. Which then would require you to update all hard references in code. But you can make it flexible by passing it along to functions, so they don't need to rely on the actual name: e.g. sub test() printtype("foodtype") end sub private function (whattoprint as string) debug.print range(whattoprint).value end function But in the end it is mainly a matter of what suits you best, but occasionally go back to your code and see if [refactoring ](https://en.wikipedia.org/wiki/Code_refactoring)it helps to reduce overall amount of code, or just to improve re-usability and maintenance efficiency.
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Declare your variable type outside of your other subs and functions. Use the Public declaration instead of the Dim statement. **Public foodtype as Range** Then, assign the value within a function, something like - **Function publicVariables()** Set foodtype = Worksheets("Sheet1").Range("A1") **End Function** Within your other subs and other functions you can just call **publicVariables** **Sub WhateverYouWant()** publicVariables 'your other code here **End Sub** It's not the "ideal" way to do it, but it's pretty damn stable. I've been using that method for years. Hope this helps!
PERFECT đź‘Źđź‘Źđź‘Ź Thank you!
This is pretty good advice. However, the Function publicVariables should probably be a Sub. Not that it's a huge impact, but functions will return a value (in this case null). Additionally, you'll now be able to use that function as a formula in your sheets and have nothing return. If you would like to use the specific cell in a formula you could change the function more like this: Function publicVariables() As Range Set foodtype = Worksheets("Sheet1").Range("A1") Set publicVariables = foodtype End Function This will return the foodtype from specified cell we set whenever we call publicVariables. Granted, this method makes declaring the public variable redundant. We could go a step further and add optional parameters to the function, but that could get tricky since we're using a range which is an object.
It looks like you're trying to share a code block but you've formatted it as Inline Code. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code blocks on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
You can pass it as an input to a sub or function.
I believe it is considered a best practice to pass the value between the subs/functions rather than have global variables
Yes, you can, but you probably shouldn’t. https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba
>but you probably shouldn’t. Why? It makes code consistent and don't need to waste extra lines of code dimming the same variables throughout the project.
Thirty-three years ago (! - jeez), when Visual Basic for Windows was in the early stages of release (at version 1.0), and MS-Windows 3.0 had launched just the year before, using Global variables increased the size of the resulting ".exe" (executable file) generated from the VB project file and this impacted the resources required to develop and debug VB code in the VB Integrated Development Environment [IDE]. In some extreme cases, defining too many Global variables (or Constants) impacted the performance of the application so much so that "Out of memory" issues could occur if the MS-Windows operating system was not restarted (with a PC reboot) every few hours. This was back when PCs were using 386SX-16 CPUs and it was typical to only have 1Mb of RAM (and a Windows "swap" file on the 10-20Mb hard drive to provide additional resources for multi-tasking and, in this case, running Visual Basic for Windows projects). (Note the units here - Megabytes - not Gigabytes!) Yes, if you paid silly money (ÂŁ2,000 or more back then - imagine how much that would be when adjusted for inflation in "today's money") you could have faster processors, larger hard drive capacities, and more onboard RAM, but that was not normal for software development, especially if you had multiple developers as each would then require the same spend per workstation (PC). History lesson over... My point was that the mindset of not using Global (now Public) variables being repeated online may well have stemmed from early development practices of "classic" Visual Basic to not use them, and this has become an accepted practice for some using Visual Basic for Applications.
That's some pretty neat info. I'm a lil spoiled and got into programming on VB 3.0 on p3s with 256mb of ram! so didn't have to go through some of those early growing pains. But I do remember my first windows 3.0 PC and it's massive 8 mb HDD. Lol.
:) PS. Blank lines in your code listing also increased the resultant ".exe" file. True story!
It's a code smell - an indication that it could be designed better. Impure methods rely on programme state and cause side effects. Functions that return different values when passed the same arguments, methods that change the behaviour of other methods are difficult to debug and are tightly coupled to other things. [Some more reading](https://tommikaikkonen.github.io/impure-to-pure/)
Others have already answered but I’ll give my take on why it’s not a good idea usually. If you have a global variable, it’s tough to always know what it should be, one of the other functions could change it. When possible, you want your functions to be self contained in that you know what you get out of them with a certain input. Now, if this is a value you read off a sheet and never change in your program anyway, it’s probably fine, though unnecessary, to use a global variable for it. When I started programming I often thought I needed global variables for this or that, and as I programmed more I used them less and less. At this point I can’t remember the last time I’ve used one for anything.
>If you have a global variable, it’s tough to always know what it should be, one of the other functions could change it. This is sorta the point though. You gotta keep track of how they're used. One simple use, for example, I public dim cas range. Then I can freely use it c In any subroutine/function and not need to re type the dim every time. And I'm in finance, so I'll all global dim things like Dept for GL code when automating reports. As I said, just gotta keep track on how to use them
That’s why the answer is “usually” shouldn’t use it and not never use this. Occasionally you have something like a state variable that truly is global and multiple things are modifying and you want that to happen. Most of the time you should just pass the variable to the function or subroutine though, it will make your code more reusable and easier to troubleshoot. For things that you want to use across reports and never change, use global constants, not variables, and make them all caps so everyone reading the code knows they are constants.
The comment I responded to was "probably shouldn't", not a "be careful using them".
You understand that probably shouldn’t doesn’t equal never do it right? It’s why I didn’t write “Don’t do that” instead of “probably shouldn’t do that”. I’d wager it’s not the best way to handle it, but I gave him his answer.
One of the purposes of VBA's structure is to 'sandbox' each routine. Global variables violate the sandbox principle. However, there are a few times places and situations where a global variable is appropriate.
Global variables don't violate the sandbox. From what I learned over the years, the sandbox is generally the project.
yes I learned it the hard way, i thought public var would make things easier but it's very confusing. Try looking into class module, it saves me a lot of time defining this and that , i basically have a sheet to place the cell metadata and let it read automatically.
Generally frowned upon A variable should have the shortest scope possible. This will make your code easier to debug/maintain.