CODEWITHSUNDEEP
excelvba
Jabberwocky
Jabberwocky

Reputation: 50802

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

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 ?

Upvotes: 1

Views: 133

Answers (1)

Rory
Rory

Reputation: 34055

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.

Upvotes: 1

Related Questions