Problem with Public variable

[JoeS01]

Hello, in ThisWorkbook, when the workbook opens, it declares some variables public, and then subsequently assigns values to these variables.

In a subsequent module that requires the values of these public variables, the values are non-existent.

Can anyone please advise where I am going wrong?

THISWORKBOOK CODE

Option Explicit

Public strMasterDBPath As String
Public strDBFileName As String
Public conBase As ADODB.Connection


Public Sub Workbook_Open()

ThisWorkbook.Activate 'temporary for ease of restarting the workbook

'create public variables:

strMasterDBPath = ThisWorkbook.Path
strDBFileName = "Master_Database.mdb"
.
.
.

MODULE CODE

Option Explicit

Public Const MODULE_NAME As String = "Util_Import_Master"


Sub Fetch_Master_Data()

Dim strFullPath, strPath, strMsg As String
Dim objAccess_Master As Access.Application


' Description:-
' This process takes the required data from the Master DB on the network drive
' and loads it into the Excel workbook.


strFullPath = strMasterDBPath + strDBFileName <<<< ERROR: All string variables empty

Application.DisplayAlerts = False

Set objAccess_Master = CreateObject(strFullPath)

.
.
.

 

[hiker95]

JoeS01,

Sorry, wrong post.

 

[Ruddles]

Declare them in a standard code module.

 

[JoeS01]

Thanks for your help Ruddles, very much appreciated.
I tried your suggestion, unsuccessfully. I got a compilation error saying the variables did not exist:

modified THISWORKBOOK code

Call newModule.createPubVariables

NEW MODULE CODE

Option Explicit
Public strMasterDBPath As String
Public strDBFileName As String

Public Sub createPubVariables()

'These two variables are used throughout this Project

strDBFileName = "Master_Database.mdb"
strMasterDBPath = ThisWorkbook.Path

End Sub

EXISTING MODULE CODE

Option Explicit
Public Const MODULE_NAME As String = "Util_Import_Master"
Sub Fetch_Master_Data()

Dim strFullPath, strPath, strMsg As String
Dim objAccess_Tx_Master As Access.Application


' Description:-
' This process takes the required data from the Master DB on the network drive
' and loads it into the Excel workbook.


strFullPath = strMasterDBPath + strDBFileName <<< Compilation ERROR : Variable not defined

Application.DisplayAlerts = False

Set objAccess_Tx_Master = CreateObject(strFullPath)

 

[Hermanito]

Hey Joe,

the problem with the public variables is not that they have not been created, they just have not been given a value when you try to retrieve them.

This might be a workaround for you:

create a separate module, called modInit or something where you keep all your public variable initialisation code together.
For each 'public variable' you want, create a public function instead, for instance:

Public Function GetMasterDBPath As String
   If strMasterDBPath = "" Then
      strMasterDBPath = TheValueYouWantToInitialiseThisWith
   End If
   GetMasterDBPath = strMasterDBPath
End Function

You can declare the strMasterDBPath in the same init module as a private variable. The first time you call the function, the value will be assigned, all subsequent calls will just return the assigned value as is.

With this method you have effectively encapsulated your public variables behind an interface:the functions you call in the rest of your code.

In other places of your code you can then use this as follows (I changed your + into &, it's better to do string concatenation in VB6 with &)

strFullPath = getMasterDBPath & getDBFileName

If you want to change how you define the master DB path and filename (for instance read it from a .ini file, declare them as constants, ...), you can easily do so without having to change a single line in the rest of your code. You would only need to adapt the code in the init module functions.

 

[JoeS01]

thanks Hermanito, that worked well