Need Global Variable for multiple modules

[Rudi]

Hi,

I need to store a number that must be available for various macros over multiple modules and for multiple runs. I am not too familiar with global constants and if their value can be stored between different runs, but in my search I can across this on Chip Pearsons site, and modified it to store a row number (the part in blue:

Application.ExecuteExcel4Macro "SET.NAME(""gblNewDataStart""," & Range("A1048576").End(xlUp).Offset(1, 0).Row & ")"

This is working very well, but just as a learning curve, is there another way that this can be done; to store a value to use between different runs of different macros?
TX

[HansV]

The hidden name space is a memory area in which you can store values that will be available to the entire application during the current Excel session.
When the user quits Excel, the memory area is cleared.

It seems a bit strange to store the value Range("A1048576").End(xlUp).Offset(1, 0).Row, which is specific to the active worksheet in the active workbook, in an application-level name.

If you define a constant or variable as Public in a standard module, that constant/variable will be available to all procedures and functions in the same workbook while it is open. When the workbook is closed, public constants and variables are removed from memory.

If you want to preserve a value in between sessions, you can store it in a cell in a worksheet (which can be hidden), or you can store it in a custom document property - see the help for CustomDocumentProperties. Chip Pearson has a page on it too: Document Properties.

[Rudi]

Thanks for your reply.
RE: the comment about strange to store value. The Range("A1048576").End(xlUp).Offset(1, 0).Row is storing a row number that will be reference in 2 or three different macros to access a position in a sheet. Each macro runs separately and is stored in a different module.

TX... I tested a Public Variable called:
Option Explicit
Public intNewDataStart As Integer

and this is also working between the macros too...

I notice that when I reset the project (using the reset button on the VBE toolbar) it also clears the public variable. So to confirm, doing the reset and closing the workbook is the only time the variable loses its value? If this is the case, I don't need the application level value.

TX

[HansV]

Yes, private (module-level) variables and public (workbook-level) variables keep their value until the workbook is closed or until the VBA project is reset (for example because an unhandled error occurs).

[Rudi]

Gotcha.