Is there such a thing as user defined constants simolar to user defined functions?

Question

I have an Excel workbook with a VBA module containing lots of user defined functions that are extensively used by formulas in the different worksheets.

So I can write for example =MyFunction(A1; A2; 5). This works well.

Now I'd like to have user defined constants so I could write:

=MyFunction(A1; A2; myConstant)

The only way I found to do this, is putting all my constants in named cells in a separate worksheet. This works well but it's awkward.

Another possibility is writing a user defined function for each constant such as:

Function myConstant()
    myConstant = 5
End Function

This is also awkward, and then the constants must be followed by (), e.g.

MyFunction(A1; A2; myConstant())

I tried to put the constants at the beginning of the module that contains by user defined functions:

Const myConstant As Integer = 5

but this only allows be to use the constants in VBA code, but not in Excel formulas.

Does anybody have an idea ?

Rory Rory · Accepted Answer · 2015-09-17T14:28:25

You don't need to put them in cells. You can define names that refer to values as well as ranges. Just put =5 for example in the RefersTo part of the Name Manager.

Is there such a thing as user defined constants simolar to user defined functions?

1 Answers