CODEWITHSUNDEEP
excelvba
Jshelby
Jshelby

Reputation: 13

File name across several modules

I have 5 different modules that contain the same file name. Example would be Module 1-5 have this code sFile = "Data File for CIP Split.xlsx" I would like to update this file name for all modules once but cannot figure out how to do so.

have tried to call it public but might not have done it correctly 

Sub ALL_Run_First()

    Application.ScreenUpdating = False
    Dim Wb As Workbook
    Dim usedrng As Range, lastrow As Long
    Dim sFile As String
    Set usedrng = ActiveSheet.UsedRange
    lastrow = usedrng(usedrng.Cells.Count).Row

    sFile = "Data File for Test Split.xlsx"     
    sFile1 = "Test Template.xlsm"        

    Application.DisplayAlerts = False     

    If CheckFileIsOpen(sFile) = False Then        
        Workbooks.Open stPath & sFile, UpdateLinks = False
    End If
    Application.DisplayAlerts = True

When I try and do the public const I get the file cannot be found. Module 1-5 have this same code, so I would like to have it update all modules when I change the file name (usually once a year)

Thanks to everyone that helped out it was really appreciated! I created a hidden settings sheet and had it reference that sheet in the code.

sFile = ThisWorkbook.Sheets("Settings").Range("A2").Value

SFile1 = ThisWorkbook.Sheets("Settings").Range("A3").Value

I updated all the modules to use this and now I will just have to update the settings sheet and it feeds the rest of the modules.

Upvotes: 1

Views: 48

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Just to provide an alternative to @dwirony's excellent answer - semantically, a file name isn't a Const (Const Pi = 3.14159 would be one), ...especially if it needs to be updated every year!

That value is data, not code. The best solution is to take it out of the code entirely. One way to achieve this could be to have a (hidden?) "settings" sheet, with a specific named cell (say, DataFilePath) that contains the file name to use.

You can then name that "settings" sheet (set its (Name) property to e.g. SettingsSheet), and then in that sheet's code-behind, you can expose a public property that gets you the value of that cell:

Public Property Get DataFilePath() As String
    DataFilePath = Me.Range("DataFilePath").Value
End Property

Now in the 5 places where the file name is hard-coded, replace it with SettingsSheet.DataFilePath.

Next time the filename needs to change, no code needs to be modified - only the contents of the cell that's named DataFilePath on this SettingsSheet worksheet.

Upvotes: 2

dwirony
dwirony

Reputation: 5450

It looks like what you're looking for is a Public Const - see below:

Module 1:

Public Const mystring = "Hello!"
Sub Test()

'There's nothing here!

End Sub

Module 2:

Sub Test2()

MsgBox mystring

End Sub

And running Test2 produces:

img1

Upvotes: 3

Related Questions