Martin Yeo

Excel: Global Constants

Improve simplicity by consolidating all a workbook’s dynamic settings into a single userform…

---

Do you ever maintain a workbook that contains a number of dynamic reports, each with a variety of settings?

Consider holding all settings on a dedicated tab, changed via a VBA userform.

• Some settings will be shared between report pages. Combining these settings makes for a more intuitive workbook
• Clutter is reduced on the report pages themselves
• There is no need for hidden space on each report for any extra calculations that stem from the settings (e.g. previous month / previous year)
• The dedicated settings page gives unlimited space to lay out the settings and any further calculations
• The settings are simpler to work with and add to when they are all in one place

Since all formulae will now be referring to another worksheet, an important consideration is to keep the name of that worksheet as short as possible (e.g. AA).

The VBA code to operate the userform is very short, e.g.:

Private Sub UserForm_Initialize()

  Dim c As Range

'clear any previous genera in the genus combobox then add all genera from the
genus list 
  Combo_Genus.Clear
  For Each c In Sheet1.Range("Genus")
    Combo_Genus.AddItem c.Value
  Next c

'set each control to the relevant value in the global constants tab
  Combo_Genus.Value = Sheet2.Range("Set").Value
  Date_Start.Value = Format(Sheet2.Range("Date_Start").Value, "dd/mm/yyyy")
  Date_End.Value = Format(Sheet2.Range("Date_End").Value, "dd/mm/yyyy")

End Sub

Private Sub Button_Set_Click()

'set each global constant to the relevant control value
  Sheet2.Range("Set").Value = Combo_Genus.Value
  Sheet2.Range("Date_Start").Value = CDate(Date_Start.Value)
  Sheet2.Range("Date_End").Value = CDate(Date_End.Value)
  
  End

End Sub

Here you can click to download examples of a reporting workbook using local constants and one using global constants. Macros must be enabled for the global constants workbook to function.