I'm maintaining some VBA code for Excel 2003, developed by a person no
longer employed where I work.

This code contains several small forms, and work sheets.

One the main worksheet, there are several command buttons, (and the
event handlers for these buttons as well)

I want to add a version number to the program, so that the string
variable is defined as a constant in the VB code, but is displayed on
the main worksheet.

I've defined the constant as follows
Private Const ProgramVersionNumber As String = "1.1"

Now, I want to display this in a cell on the main worksheet.

1. How do I print/display the value of this variable (from the VB
code) in Cell A1, ?
2. Can I reference this variable (or any variables in the VB code)
from the worksheet? If so, what is the syntax for doing this?
I've tried entering =ProgramVersionNumber in Cell A1, but that doesn't
work.

I could use some beginners references for Excel VBA.

I'm pretty comfortable with Visual Basic (VB6 to be precise), but I
rarely work with Excel, let alone driving Excel from VBA code.

thanks

You can't directly read the version number from a worksheet cell. You need a
function in a module (create from "Insert" menu in VBA) that will read the
constant and return its value to the worksheet cell. E.g.,

Public Function GetVersion()
GetVersion = ProgramVersionNumber
End Function

Then, you can call this from a worksheet cell with

=GetVersion()


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)



"cappy2112" wrote in message
ups.com...

I'm maintaining some VBA code for Excel 2003, developed by a person no
longer employed where I work.

This code contains several small forms, and work sheets.

One the main worksheet, there are several command buttons, (and the
event handlers for these buttons as well)

I want to add a version number to the program, so that the string
variable is defined as a constant in the VB code, but is displayed on
the main worksheet.

I've defined the constant as follows
Private Const ProgramVersionNumber As String = "1.1"

Now, I want to display this in a cell on the main worksheet.

1. How do I print/display the value of this variable (from the VB
code) in Cell A1, ?
2. Can I reference this variable (or any variables in the VB code)
from the worksheet? If so, what is the syntax for doing this?
I've tried entering =ProgramVersionNumber in Cell A1, but that doesn't
work.

I could use some beginners references for Excel VBA.

I'm pretty comfortable with Visual Basic (VB6 to be precise), but I
rarely work with Excel, let alone driving Excel from VBA code.

thanks

On Jun 18, 12:02 pm, "Chip Pearson" wrote:
You can't directly read the version number from a worksheet cell. You need a
function in a module (create from "Insert" menu in VBA) that will read the
constant and return its value to the worksheet cell. E.g.,

Public Function GetVersion()
GetVersion = ProgramVersionNumber
End Function


Thanks-
Using a function makes better sense than accessing a variable (or
const) directly.

On Jun 18, 12:02 pm, "Chip Pearson" wrote:
You can't directly read the version number from a worksheet cell. You need a
function in a module (create from "Insert" menu in VBA) that will read the
constant and return its value to the worksheet cell. E.g.,

Public Function GetVersion()
GetVersion = ProgramVersionNumber
End Function

Then, you can call this from a worksheet cell with

=GetVersion()

Ok- I've tried this, but the spreadsheet still shows #NAME?

Public Function GetVersion()
GetVersion = ProgramVersionNumber
End Function

The code must be in a regular code module (create from Insert menu in VBA)
in the same workbook as the sheet from which you are calling the function.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"cappy2112" wrote in message
ps.com...
On Jun 18, 12:02 pm, "Chip Pearson" wrote:
You can't directly read the version number from a worksheet cell. You
need a
function in a module (create from "Insert" menu in VBA) that will read
the
constant and return its value to the worksheet cell. E.g.,

Public Function GetVersion()
GetVersion = ProgramVersionNumber
End Function

Then, you can call this from a worksheet cell with

=GetVersion()

Ok- I've tried this, but the spreadsheet still shows #NAME?

Public Function GetVersion()
GetVersion = ProgramVersionNumber
End Function

On Jun 18, 12:32 pm, "Chip Pearson" wrote:
The code must be in a regular code module (create from Insert menu in VBA)
in the same workbook as the sheet from which you are calling the function.

Not sure I understand what is different from what exists now, and what
you are suggesting.

There is already existing VBA code associated with this workbook-
specifically, the worksheet in question.
That is- when I double click on a command button on that worksheet,
the VBA event handler is displayed.

Is this relationship between workbook/worksheet & VBA code what your
referring to ?

Do NOT put the code in the code module associated with a worksheet. Re-read
my replies. You need to CREATE A NEW MODULE.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"cappy2112" wrote in message
ups.com...
On Jun 18, 12:32 pm, "Chip Pearson" wrote:
The code must be in a regular code module (create from Insert menu in
VBA)
in the same workbook as the sheet from which you are calling the
function.

Not sure I understand what is different from what exists now, and what
you are suggesting.

There is already existing VBA code associated with this workbook-
specifically, the worksheet in question.
That is- when I double click on a command button on that worksheet,
the VBA event handler is displayed.

Is this relationship between workbook/worksheet & VBA code what your
referring to ?