CODEWITHSUNDEEP
excelvba
wit4r7
wit4r7

Reputation: 135

Defining cross-module variables with values

I'm developing a bunch of Excel Macros for making my life easier. One part of different macros is inserting a picture into sheets. For this reason, I would like to save the path to the images in a global location and then access it via a variable (so that I don't have to manually adjust the paths in every macro if it changes). I use one module per macro

In my own module "Variables" I defined a variable as Public or Global and then assigned a value via a sub. If I now access this variable via another module, I get an empty MsgBox. For test purposes I use a string which I want to display via an MsgBox.

Modul 1: 

Public test As String
Sub variablen()
    test = "String for Test "
End Sub

Modul 2: 

Public Sub testpublic()
    MsgBox (test)
End Sub

Upvotes: 2

Views: 737

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

so that I don't have to manually adjust the paths in every macro if it changes

If it ever needs to change, then it semantically isn't a Const. The key to writing code that you don't constantly need to modify is to separate the code from the data.

A file path that sometimes needs to change can be seen as some kind of configuration setting.

Have a module that is able to read the settings from wherever they are, and return the value of a setting given some key.

The settings themselves can live on a (hidden?) worksheet, in a ListObject table with Key and Value columns, and looked up with INDEX+MATCH functions (using the early-bound WorksheetFunction functions will throw run-time errors given a non-existing key string):

Option Explicit

Public Function GetSettingValue(ByVal key As String) As Variant
    With SettingsSheet.ListObjects(1)
        GetSettingValue = Application.WorksheetFunction.Index( _
            .ListColumns("Value").DataBodyRange, _
            Application.WorksheetFunction.Match(key, .ListColumns("Key").DataBodyRange, 0))
    End With
End Function

The Variant will retain the subtype of the Value, so for a String value you get a Variant/String; for a Date value you get a Variant/Date, for a numeric value you get a Variant/Double, and for a TRUE/FALSE value you get a Variant/Boolean.

Now when the file path needs to change, your code does not:

Dim path As String
path = GetSettingValue("ImageFolderPath")

And if you need more settings, you have no code to add, either:

Dim otherThing As String
otherThing = GetSettingValue("OtherThing")

All you need to do is to make sure the string keys being used match the contents of the Key column in your SettingsSheet.

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

I recommend to use a constant instead of a variable:

Module 1

Option Explicit

Public Const MyPath As String = "C:\Temp"

Module 2

Option Explicit

Public Sub ShowPath()
    MsgBox MyPath
End Sub

I also recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

If you do it like you did test is empty until it was initialized by running the procedure variablen first. If you use Public Const no initialization is required.

Upvotes: 2

Related Questions