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

If you want CurrentName to be visible throughout your project, you might need to change Private to Public. I don't think that (as it is) Workbook.Open would be able to see that variable (assuming that this code is in a standard code module). — John Coleman
– John Coleman, Commented Nov 24, 2017 at 14:02

Vityata · Accepted Answer · 2017-11-24 14:06:29Z

2

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

edited Nov 24, 2017 at 14:06

answered Nov 24, 2017 at 14:00

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vityata Over a year ago

Thank you, @Gary'sStudent :)

Imran Malek · Accepted Answer · 2017-11-24 14:35:26Z

1

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

edited Nov 24, 2017 at 14:35

answered Nov 24, 2017 at 14:18

Imran Malek

1,7392 gold badges13 silver badges14 bronze badges

Collectives™ on Stack Overflow

Variable with InputBox vs multiple subs in a module

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related