How to use constants stored in VBA in a regular formula?

**ESF** · 02-01-2023, 03:23 AM

Hello everybody,

My google skills abadon me here.... I tried to search for keywords like "use constants stored in VBA in a regular formula". A lot of hits of course but not the constants I'm looking for.

The case: I'd like to hide a table of steel profiles in VBA and let the user of the model use them as if they were named cells in a formula. I'd prefer not to make a table in an Excel sheet and hide it (or very hidden).

I tried:

Please Login or Register  to view this content.

example1.png

But Excel doesn't recognise these. Could it maybe has something to do with the name? Maybe Excel thinks this is a coordinate?
However HEB_200 doesn't work either:

Please Login or Register  to view this content.

example2.png

Which gives #NAME?
example3.png

Does anyone have a suggestion to help me?

Thank you in advance!

**skywriter** · 02-01-2023, 04:04 AM

You can add names to your worksheet and you can read this article to get further details.
Apparently you can also hide them so they don't show up in the name manager sheet.

Please Login or Register  to view this content.

**jindon** · 02-01-2023, 04:05 AM

You need to declare global variable in declaration part in the code module, not in the procedure.

e.g

Please Login or Register  to view this content.

**ESF** · 02-01-2023, 06:16 AM

Thank you for your swift answers! I was hoping for a solution the users don't need VBA for. Everywhere in the calculation one should be able to use steel sections in their calculations. Like native Excel formulas as being created by custom formulas. Maybe a bit like =7*Pi(). Pi is a constant everybody can use in Excel.

Creating named cells isn't feasible as buildings or bridges may contain many different profiles:
example4.png

**jindon** · 02-01-2023, 06:25 AM

Do you want it as an UDF?

e.g.

Please Login or Register  to view this content.

In cell
=D6*myPi()

**MrShorty** · 02-01-2023, 10:24 AM

If you don't want to use VBA, you can create a named constant using the "define name" dialog: https://www.techrepublic.com/article...med-constants/

I have used jindon's UDF approach, too. In those few cases, they are constants that I want available to all of my worksheets, and I don't want to bother defining a named constant each time I create a new workbook. In those cases, not only do I use a VBA UDF, but I have stored the VBA UDF inside of an add-in that I can install and make those constants (and other UDFs I need) available to all of my workbooks.

**skywriter** · 02-01-2023, 05:49 PM

Creating named cells isn't feasible as buildings or bridges may contain many different profiles:

Just so we're clear, the solution I gave you doesn't create a named range it creates a name that's associated with a value.

Please Login or Register  to view this content.

So after running this code you have names in your workbook that a user can type into any cell and when you hit enter it will display the value and it can be used in formulas.
In the case above if a user types =HEB_200 in any cell and presses enter it will display the value.
So =HEB_200 + 1 will display 84.2
I also gave you a link to the page about names which shows that if you use visible = false the user won't see the names in the name list and therefore can't delete them by accident.

Please Login or Register  to view this content.

**ESF** · 02-02-2023, 05:20 AM

Yes, that functionality of a UDF is what I meant. However this doesn't seem to be a logical method for many hundreds of constants. I was more thinking of a table type or list structure in VBA.

Maybe a veryhidden Excelsheet than is a better way forward? I could make a macro to give all the steel profile types a name. Then everybody can just type any L or H or I profile they want. The visible = false trick was new for me. Do you see any limitations by using so many named cells? Maybe Excel will slow down?

Thank you all!

**jindon** · 02-02-2023, 06:49 AM

Still not clear though

Please Login or Register  to view this content.

=D6*myPi("HEB280")

**ESF** · 02-03-2023, 04:22 AM

This example is really good and as I had in mind:

Originally Posted by jindon

=D6*myPi("HEB280")

I think I'll start small with the 'case' example. Instead of trying to be comprehensive with the full table Steel sections.xlsx

With your help I've made different functions and it works great! Thank you all!

Please Login or Register  to view this content.

=D6*wbHEB(360)

**ESF** · 02-03-2023, 10:35 AM

Sorry to come back to my already solve question this morning. The tool works as a charm, so I thought to expand the functionality...

I tried to assign multiple constants to a single variable. In this case the mass ánd surface area of a profile.
Unfortunately now the user can't just type the size but need to use ""

Please Login or Register  to view this content.

My workbook now only shows the area, not the mass:
109.3 =wbSheetpile("AZ18-700","")
0 =wbSheetpile("","AZ18-700")

Any suggestions are highly appreciated!

**karedog** · 02-03-2023, 12:06 PM

Maybe :

Please Login or Register  to view this content.

**ESF** · 02-04-2023, 07:12 AM

Oops... an ara is a macaw in Dutch... sorry for this blooper.

**karedog** · 02-04-2023, 08:26 AM

Not to worry about it.

How to use constants stored in VBA in a regular formula?

LinkBack

Thread Tools

Rate This Thread

Display

How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Re: How to use constants stored in VBA in a regular formula?

Thread Information

Users Browsing this Thread

Similar Threads

Use constants in a formula

UDF intellisense constants in formula bar. Possible with vba?

Counting constants between constants

Converting weeknumber formula to VBA (array constants)

Copying formula cells that really ought to be constants?

Formula Array Constants, Problem

How do you copy a formula without incrementing some constants?

Bookmarks

Bookmarks

Posting Permissions