Declaring public constants

[hershmab]

In my VBA projects I often use message- or input-boxes that Iwant to place in specific positions on the current window. After many years of writing VBA macros, I have finally discovered that the related functions or methods have optional arguments to control this and also that the values have to be specified as X- and Y-coordinates in units called TWIPS.
With some experimentation I have discovered what the co-ordinates are for the top left and bottom right of any window.
I want to make these values available as public constants (i.e. as freely as in built VBA constants) to any of my projects. Where should I declare them to achieve this:

    - in Personal.xlsb?
    - in an .XLAM file where I keep all my own user-defined functions?
    - in either case, in which (type of) module?

[HansV]

Welcome to Eileen's Lounge!

Either Personal.xlsb or your add-in (.xlam) will do. The following steps apply to whichever one you prefer.

- Activate the Visual Basic Editor.
- Select the one you want to use (Personal.xlsb or your add-in) in the Project Explorer on the left hand side.
- Give the VBA project a unique name - not the default VBAProject, because that would conflict with most workbooks.
- For example, you could name the VBA project in your personal macro workbook Personal.
- Insert a standard module (Insert > Module).
- Declare your variables, e.g.

Public Const MyTop = 2880

In each workbook where you want to use the constants, you will have to do the following:
- Select the workbook in the Project Explorer.
- Select Tools > References...
- Tick the check box for the VBA project name that you assigned above.
- Click OK.

You can now refer to the constants as ProjectName.VariableName. For example:

MsgBox Personal.MyTop

[hershmab]

Dear HansV,
Thanks for your prompt reply.
As both Personal and the .XLAM file are automatically added as references to all my project, I assume that I can use and simply add the relevant CONST declaration at the top of any module in either - or even a completely new one?

[HansV]

Yes, that is correct.

[Rudi]

Hi,

Welcome from me too...

Your questions made me look into some of the references you mentioned (at least for Excel VBA that is!) and I thought it worth the while to mention them here...

1. Only input boxes have the XPos and YPos options built in as arguments
   • The XPos argument (if set at 0) makes the input box appear on the left edge of the screen. Adjusting the value to >0 moves it right.
   • The YPos argument (if set at 0) makes the input box appear on the top edge of the screen. Adjusting the value to >0 moves it down.
       
2. If you want a message box to appear in a specific location on the screen, you need to access Windows API libraries in your code
   • See these examples: Tom Urtis Code and Excely Code