Microsoft 365 (and specifically Microsoft Excel) is my favourite tool when it comes to work, and also really fun to play with in my spare time as a ‘hobby’ that also helps me improve my skills.
I’ve used probably dozens of different methods of storing ‘sets’ of variables for the tools and mini programs I’ve created in Excel over the years, some very rudimentary, and others more involved but also difficult to maintain or fragile in various ways.
However, recently while playing around with Power Query and Excel formulas (yes, I do this when I let my mind wander), I came across an idea for a robust variables container stored in an Excel table that can be accessed in several different ways throughout a given tool. You can access or refer to these variables using good old Excel functions (with a LAMBDA, or a UDF if you’re old school), with a simple Power Query function call, or a few lines of VBA in your editor (see the UDF), depending on the needs of your project.
Getting Started
Let’s go through the basics of the project’s needs first:
This table will contain all the variables we want to reuse across different places in the tool or want to be user-configurable later on.
1. The LAMBDA function
First, we can create the LAMBDA function (if you use Excel 365, which I highly recommend!), by putting the following function in the Name Manager (I used the name getVariable for these functions, and variableName for the name of the variable):
=LAMBDA(variableName, XLOOKUP(variableName, Variables[Name], Variables[Value], "No matching variable.", 0))Using this function elsewhere in the workbook, we get, just like we want:
2. The Power Query function
Next up, we will create a Power Query function to be able to refer to a variable from this table and get back the value for it.
Launching the Power Query Editor (from the Data tab), I will create a function called getVariable (again), and include the following:
(variableName) =>
let
Source = Excel.CurrentWorkbook(){[Name="Variables"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
Result = Record.Field(#"Promoted Headers"{0}, variableName)
in
ResultNote that I’m still using variableName for the variable here, and this allows you to invoke the function and get the expected result back if we want to refer to this variable in another function:
3. The VBA function
Finally, we can create a quick function in VBA that allows you to use these same variables across your project, making it fast and easy to maintain your code however you have it set up.
Open the VBA editor, and in a new (or existing) module, I’ll write the getVariable (again!) function:
Function getVariable(variableName As String)
Dim VariablesTable As ListObject
Set VariablesTable = Main.ListObjects("Variables")
getVariable = WorksheetFunction.XLookup(variableName, VariablesTable.ListColumns(1).DataBodyRange, VariablesTable.ListColumns(2).DataBodyRange, "No matching variable.", 0)
End FunctionWith this function, you can easily get any variable from your table anytime you need it, and you’ll be able to change the parameters in your code without digging in to the developer tools.
Well, that’s it. With this simple, basic Excel table and three short code snippets, you’re ready to tackle nearly any Excel project imaginable while keeping your data in check and making sure you don’t leave hard-coded values anywhere in your Excel tools.
