Excel VBA Public Variables are not recognized

Question

In MS Excel, I am trying to declare some public variables to use them in several modules after. But I am getting this error:

Ambiguous name detected: datebck

This error is valid for all public variables that I declared.

The modules are like this:

Option Explicit
Public LastColumnBck As Long
Public wsbck As Worksheet
Public datebck As String
Public linebck As String
Public TargetRowBck1 As Integer
Public TargetRowBck2 As Integer
Public StationRowBck As Integer


Public Sub FindLastStationColumnBck()
Dim excelfilename As String
excelfilename = "ERTP-Construction Tracking Sheet " & "(" & datebck & ")" & ".xlsx"
Set wsbck = Workbooks(excelfilename).Worksheets(linebck)
TargetRowBck1 = wsbck.Application.WorksheetFunction.Match("Sand filling", wsbck.Range("A:A"), 0)
TargetRowBck2 = wsbck.Application.WorksheetFunction.Match("Asphalt", wsbck.Range("A:A"), 0) - 1
StationRowBck = wsbck.Application.WorksheetFunction.Match("Main Activity", wsbck.Range("A:A"), 0)
LastColumnBck = wsbck.Cells(StationRowBck, wsbck.Columns.Count).End(xlToLeft).Column
End Sub

UserForm Code:

Option Explicit

Private Sub CommandButton1_Click()
Unload UsrForm6
End Sub

Private Sub CommandButton2_Click()
datebck = TextBoxDateUsrForm6
linebck = ListBoxUsrForm6
Call Module20.Backfilling (*this is another module that should use public variables also*
End Sub

I tried this type of declaration before and they were working but in this situation what is missing although structure of code is exactly the same ?

Answer 1

This experience should help you understand why it is not a good idea to use Global variables. However, if you wish to continue doing so it would make sense to put your global variables in a Class with the Predeclared ID set to true.

For example, if you create a Class called Glb which contains your Global variables (and only these variables) you get the following advantages

1. You know where the global variables are located.

2. You get easy intellisense for your global variables. e.g. typing 'Glb.' will bring up the list of your global variables which cuts down on typos and memory stress

3. If you need to do any checks on the values assigned to a Global variable this can be done in the Glb Class.

I'd recommend the use of the free and fantastic Rubberduck addin for VBA Amongst this addins many talents is the use of comments as annotations e.g. "'@PredeclaredId" which makes setting the Predeclared attribute of a Class mostly painless.

You can start your global class with just the variable declarations

'@PredeclaredId
'@Exposed
Option Explicit

Public LastColumnBck As Long
Public wsbck As Worksheet
Public datebck As String
Public linebck As String
Public TargetRowBck1 As Integer
Public TargetRowBck2 As Integer
Public StationRowBck As Integer

When you need to start adding validation code you can use Rubberduck's Field Encapsulation refactoring to transform you variables into properties

[![Field Encapsulation refactoring][1]][1]

which will give you the following code for 'free'

Option Explicit

Private Type TClass1
    LastColumnBck As Long
    Wsbck As Worksheet
    Datebck As String
    Linebck As String
    TargetRowBck1 As Integer
    TargetRowBck2 As Integer
    StationRowBck As Integer
End Type

Private this As TClass1

Public Property Get LastColumnBck() As Long
    LastColumnBck = this.LastColumnBck
End Property

Public Property Let LastColumnBck(ByVal RHS As Long)
    this.LastColumnBck = RHS
End Property

Public Property Get Wsbck() As Worksheet
    Set Wsbck = this.Wsbck
End Property

Public Property Set Wsbck(ByVal RHS As Worksheet)
    Set this.Wsbck = RHS
End Property

Public Property Get Datebck() As String
    Datebck = this.Datebck
End Property

Public Property Let Datebck(ByVal RHS As String)
    this.Datebck = RHS
End Property

Public Property Get Linebck() As String
    Linebck = this.Linebck
End Property

Public Property Let Linebck(ByVal RHS As String)
    this.Linebck = RHS
End Property

Public Property Get TargetRowBck1() As Integer
    TargetRowBck1 = this.TargetRowBck1
End Property

Public Property Let TargetRowBck1(ByVal RHS As Integer)
    this.TargetRowBck1 = RHS
End Property

Public Property Get TargetRowBck2() As Integer
    TargetRowBck2 = this.TargetRowBck2
End Property

Public Property Let TargetRowBck2(ByVal RHS As Integer)
    this.TargetRowBck2 = RHS
End Property

Public Property Get StationRowBck() As Integer
    StationRowBck = this.StationRowBck
End Property

Public Property Let StationRowBck(ByVal RHS As Integer)
    this.StationRowBck = RHS
End Property

The use of the 'Predecalred' attribute means that you get a default instance of the class i.e. you don't have to New a Glb object. Of course by setting the predeclared attribute to true you are also committing to using just the Glb instance and not creating instances. Multiple instances of the global variables are guaranteed to give you a headache.

Be warned though, by using a Class in this manner you will be taking the first tentative steps to using Objects in VBA rather than a mishmash of functions in Modules. [1]: https://i.sstatic.net/WcSMM.jpg

Answer 2

So the error says basically that datebck is duplicated. Did you define it elsewhere? Maybe search for "datebck As" in your Code, maybe you declared it elsewhere