Variable with InputBox vs multiple subs in a module

Question

This is the beginning of my code:

Private FilesPath As String
Private CostCentersPath As String
Private FinalPath As String
Private CurrentName As String
Private CostCenters As Worksheet
Private Final As Workbook
Private Template As Worksheet

Sub ReadySetGo()

FilesPath = "O:\MAP\04_Operational Finance\Accruals\Accruals_Swiss_booked\2017\Month End\10_2017\Advertising\automation\" 'path change ("automation")
CostCentersPath = FilesPath & "CostCenters.xlsx"
CurrentName = InputBox("Please adjust the final file name:", , "ABGR_2017-10_FINAL.xls.xlsx")
FinalPath = FilesPath & CurrentName

Set CostCenters = Workbooks("CostCenters.xlsx").Worksheets("Cost Center Overview")
Set Final = Workbooks(CurrentName)
Set Template = Workbooks("Template.xlsm").Worksheets("Template")


End Sub

Sub ReadySetGo is used only to assign values to variables and is called from within other subs in the module. But obviously with this method I get input box popping up every time the sub is called. Is there any other way, apart from Workbook.Open event, of passing variable's CurrentName value to other subs in the module, to avoid multiple InputBoxes?

Thanks, Bartek

user_5448626 · Answer

In general, there are plenty of good ways to do what you want, depending on the way your application is settled. Probably the easiest is simply to write:

If CurrentName <> vbNullString Then 
      InputBox("Please adjust the final file name:", , "ABGR_2017-10_FINAL.xls.xlsx")
End if

But be careful, because it may break your code somewhere. However, in the best case you will only run it once.

Another way is to save the CurrentName value in a given range and to read it from there every time:

If Len(Range("A1")) = 0 Then
    Range("A1") = InputBox("Please ...")    
End if
CurrentName = Range("A1")

In general, you can go a few steps further and introduce the Singleton pattern to your code https://en.wikipedia.org/wiki/Singleton_pattern, thus making sure that CurrentName is only assigned once. However, it may be a bit overkill in this case - How to create common/shared instance in vba

user_5272396 · Answer

Great answer by Vityata , I would declare the CurrentName as public variable however If I had to do it in the same setup then

 Static CurrentName As String
    Static HasName As Boolean
    If Not HasName Then
        CurrentName = InputBox("Please adjust the final file name:", , "ABGR_2017-10_FINAL.xls.xlsx")
        HasName = True
    End If

Variable with InputBox vs multiple subs in a module

2 Answers