First Time VBA user- really easy question :-)

[Dave Peterson]

If you still have that function (and the declaration line) in the worksheet
module, you should delete it. It's gonna do nothing but cause confusion later
on.

If your code in the worksheet (or any other module) needs that constant, then
change this:
Private Const ProgramVersionNumber As String = "1.1"
to
Public Const ProgramVersionNumber As String = "1.1"

By making the constant Public, then any procedure/function in any module will be
able to see it. (See Scope and visibility in VBA's help for more info.)

And you'll have to force that cell to reevaluate to update. Just changing the
code doesn't do this.

From xl2003's Help:

Change when and how formulas are calculated
By default, Microsoft Excel automatically recalculates formulas when the cells
that the formula depends on have changed.

Calculate a worksheet or workbook now

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last calculation,
and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless
of whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.


(The last one is not available in all versions of excel. I _think_ it was added
in xl2002, but I don't recall for sure.)

=====
One way to force all formulas to recalculate if you're having trouble.
Select all the cells
edit|Replace
what: = (equal sign)
with: =
replace all

Excel will see that you're "re-entering" each formula and will re-evaluate them.

 

[Dave Peterson]

And this line:
Public Const ProgramVersionNumber As String = "1.1"
should only appear once--in that General module.

 

[cappy2112]

If you still have that function (and the declaration line) in the worksheet

module, you should delete it. It's gonna do nothing but cause confusion later
on.

I only have it in the general module

If your code in the worksheet (or any other module) needs that constant, then
change this:
Private Const ProgramVersionNumber As String = "1.1"
to
Public Const ProgramVersionNumber As String = "1.1"

Only the function which is called from the worksheet references the
constant string, so leaving it private is ok.
The function is public

Calculate a worksheet or workbook now
Press CTRL+SHIFT+ALT+F9

This is the only key sequence that will update the Version number in
the Worksheet

It's too bad that I can''t force recalculation from the code But,
everything is working the way I need.

Thank You

 

[Dave Peterson]

You could always create another macro that recalculates everything

or even just show the immediate window in the VBE (ctrl-g).

Then type this and hit enter:
application.calculatefull

(well, if .calculatefull is in the version of excel that you're using--I've lost
track what you're running.)

 

[cappy2112]

or even just show the immediate window in the VBE (ctrl-g).
The people using the spreadsheet won't know how to use the Immediate
Window, and it would be confusing for them.

Then type this and hit enter:
application.calculatefull

I will just update the version string and manually do the Shift-Alt-
Ctrl-F9 whenever I make code changes, before distributing the new
version.

(well, if .calculatefull is in the version of excel that you're using--I've lost
track what you're running.)

2003

 

[Dave Peterson]

You're going to let the users update your UDF???

That sounds more confusing than learning about the immediate window.