First Time VBA user- really easy question :-)

[cappy2112]

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

 

[Chip Pearson]

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]

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.

 

[cappy2112]

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

 

[Chip Pearson]

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]

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 ?

 

[Chip Pearson]

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]

I understood your reply, however, that applies to the version number
function I want to call from the Spreadsheet.

Are you also suggesting that I put the existing code (the code that
was in place before I started maintaining this project)
in a new module as well?

 

[Dave Peterson]

The code for the UDF belongs in a General module if you want to call it from a
formula in a cell.

If you have that same code behind a worksheet or behind ThisWorkbook, it
probably can be deleted with no problems--but you'll want to test.

As for the rest of the code, I didn't see anything that would indicate that it
belongs in one spot or another. So it would be difficult to guess.

 

[cappy2112]

The code for the UDF belongs in a General module if you want to call it from a
formula in a cell.

If you have that same code behind a worksheet or behind ThisWorkbook, it
probably can be deleted with no problems--but you'll want to test.

As for the rest of the code, I didn't see anything that would indicate that it
belongs in one spot or another. So it would be difficult to guess.

I'd rather have all of the code in the same module, and delete the
code from the worksheet.
I'm pretty sure it can be deleted and moved. It's only a few
functions.

 

[cappy2112]

Well, moving all the code to the .BAS module doesn't work, like I
expected. The code in the .BAS module isn't being called at all.

First of all, I don't know where that file is saved. The VB editor
doesn't allow me to Save As, so it's not in the directory where the
spreadhseet is.

So I've got to reorganize the project somehow.

 

[cappy2112]

Well, moving all the code to the .BAS module doesn't work, like I
expected. The code in the .BAS module isn't being called at all.

First of all, I don't know where that file is saved. The VB editor
doesn't allow me to Save As, so it's not in the directory where the
spreadhseet is.

So I've got to reorganize the project somehow.

 

[Dave Peterson]

If the code is all associated with UDFs called from a worksheet, then all the
code belongs in a General module.

Select your project (in the VBE)
Insert|Module

Then back to the worksheet's code module.
ctrl-a (to select all the code)
ctrl-c (to copy all the code)

Back to the new general module.
ctrl-v (to paste the code)

You don't need to export any code to a .BAS file (unless you wanted to back it
up into a text file).

 

[Dave Peterson]

Instead of using ctrl-c to copy the original code, use ctrl-x to cut the code.

Then you don't have to go back to clean it up.

Sorry,

 

[cappy2112]

If the code is all associated with UDFs called from a worksheet, then all the

code belongs in a General module.

So I wrote a 1 line function based on Chips suggestion, and put that
in the new general module.
That works. I was able to call it from the spreadsheet.

From the project point of view, that module file looked like a .BAS

file in a VB6 project.
So I thought it would be better to have all of the code in one file.
When I moved the worksheet code to the module, the worksheet doesn't
run (obviously). So back it goes (except for the 1line function which
is actually called from the Worksheet.

Now- the function called from the spreadsheet- it shows the string
returned from the function in the general module.
However, when I update the string in the module, the string displayed
in the spreadsheet does not update, unless I delete the function call
from the cell, save the worksheet, then past the function call in to
the cell again.

How can the function call be updated whenever the string in the module
is actually updated?

 

[Dave Peterson]

That's why you don't want two procedures/UDFs in your workbook's project.

You want the UDF code in a General module--including this line:
Private Const ProgramVersionNumber As String = "1.1"

Your code is still in just a single file. It's in the project of that .xls
file. You can have multiple worksheets (each with its own code module). You
can have multiple General modules (nice for organization of procedures).

But unless you're really, really smart and organized, you don't want two
procedures that share the same name. (I wouldn't do this even if I were really,
really smart and organized!)

=====
I'm not sure what you mean by the worksheet didn't run anymore when you moved
the code.

Maybe you moved code that's associated with events or controls from the control
toolbox toolbar that are placed on that worksheet.

It's still difficult to tell since you haven't shared that code.

 

[cappy2112]

Your code is still in just a single file. It's in the project of that .xls

file. You can have multiple worksheets (each with its own code module). You
can have multiple General modules (nice for organization of procedures).

But unless you're really, really smart and organized, you don't want two
procedures that share the same name.

I wasn't suggesting this.

I'm not sure what you mean by the worksheet didn't run anymore when you moved
the code.

Well, ALl of the event handlers for the worksheet were moved to the
general module, so what's left are no event handlers.
I never got the reply from Chip about putting all of the code in the
general module, or just the one for the version number, so I tried it
both ways. Now I see that's wrong, and I'm on an even keel now.

Maybe you moved code that's associated with events or controls from the control
toolbox toolbar that are placed on that worksheet. Exactly.

It's still difficult to tell since you haven't shared that code.

It's code from my work, I can't post that. Yes, I know it makes it
extremely hard to give advice when you can't see the code, but you've
got me back on track.

 

[Dave Peterson]

Good to hear that you've got it working.

As a general rule, I only put those worksheet events and code for the controls
inside a worksheet's module.

I sometimes break that rule if there is a sub/function that will only be called
by one of those procedures. But if there's even the slightest chance that I'd
use that sub/function elsewhere, I move it to a General module.

 

[Dave Peterson]

ps.

I don't think that Chip could guess what to do with that original code without
seeing the code--or at least the procedure names.

But Chip does have some very nice instructions on the kind of worksheet events
that have to be under the worksheet:
http://www.cpearson.com/excel/events.htm

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[cappy2112]

Good to hear that you've got it working.

Mostly....

The function that is called from the Worksheet- shows the string in
the Worksheet that is
returned from the function in the general module.

However, when I update the string in the module, the string displayed
in the spreadsheet does not update, unless I delete the function call
from the worksheet cell, save the worksheet, then past the function
call in to
the cell again.

How can the function call be updated whenever the string in the module
is actually updated?