How to avoid repeating when setting the same variables for different Modules-CodePudding

This has been bothering me for a while now. I know how to declare variables Public but I am just wondering if there is a way to "Set" variables Public.

Somehow, I think having to set the same variables CurrYear, CurrMonthNr, CurrMonthName and WCSListe in multiple Modules just to check the same path is too much.

Any help is appreciated.

Nick

Option Explicit
Public CurrYear As Double, CurrMonthNr As String, CurrMonthName As String, WCSListe As String

'Tried this, but not working
'Public CurrYear = Format(Date, "YYYY")
'Public CurrMonthNr = Format(Month(Date), "00")
'Public CurrMonthName = Format(Date, "MMMM")

Function FileExists(FilePath As String) As Boolean  ' Checks if a File exists

Dim TestStr As String
TestStr = ""
On Error Resume Next

TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
    FileExists = False
Else
    FileExists = True
End If
End Function



Sub Prnt_6am() ' Sub 1

CurrYear = Format(Date, "YYYY")
CurrMonthNr = Format(Month(Date), "00")
CurrMonthName = Format(Date, "MMMM")

WCSListe = "K:\A\B\_Public" & "\Test " & CurrYear & "\" & CurrMonthNr & " " & CurrMonthName & 
           "\" & "Test " & Date & ".xlsm"

   If ThisWorkbook.ReadOnly Or FileExists(WCSListe) = False Then  Exit Sub      

End Sub



Sub PrntButton() ' Sub 2

CurrYear = Format(Date, "YYYY")
CurrMonthNr = Format(Month(Date), "00")
CurrMonthName = Format(Date, "MMMM")

WCSListe = "K:\A\B\_Public" & "\Test " & CurrYear & "\" & CurrMonthNr & " " & CurrMonthName & 
           "\" & "Test " & Date & ".xlsm"

    If FileExists(WCSListe) = False Then  Exit Sub      

End Sub

CodePudding user response：

If they are public, they should retain their values and only set them once.

But another way is to put them in their own module, and make them properties or functions. For example, in a Standard module named Settings:

Property:

Public Property Get CurrYear() As String
    CurrYear = Format(Date, "YYYY")
End Property

Function:

Public Function CurrYear() As String
    CurrYear = Format(Date, "YYYY")
End Property

The call is the same for both, but the call to the function supports brackets as well (optional in VBA if no args are being passed).

Settings.CurrYear

Settings.CurrYear()

The module name is not required if the name does not exists anywhere else in the project (including built-in types), but I always use it for clarity.

CodePudding user response：

Instead of making a large number of individual variables public, you can transfer the formation of a daily updated file name to a function. and greatly simplify all procedure condition checks to e.g.

a) If ThisWorkbook.ReadOnly Or fileExists(CurrFile) Then ... and
b) If fileExists(CurrFile) Then ...

by streamlining code via a combined date insertion Format(Date, "yyyy""\""mm mmmm") as follows:

Private Function CurrFile() As String
    CurrFile = _
        "K:\A\B\_Public" & "\Test " & _
        Format(Date, "yyyy""\""mm mmmm") & _
        "\" & "Test " & Date & ".xlsm"
End Function

Help function fileExists()

Public Function fileExists(FullPth As Variant) As Boolean
    If Len(Trim(FullPth)) = 0 Then Exit Function
    On Error GoTo oops
    If Not Dir(FullPth, vbDirectory) = vbNullString Then fileExists = True
Exit Function
oops:
    On Error GoTo 0
End Function