Value returned by function does not update automatically

  • Thread starter Thread starter mg
  • Start date Start date
M

mg

Hi , all
First of all i'm newbie to VBA Programming , and my english isn't
perfect too , but i hope someone can help me


I've written very simple function to get part of the spreadsheet name

Function FileNm(Optional Nbr As Integer = 4)
FileNm=WorksheetFunction.Substitute(ThisWorkbook.Name,Right(ThisWorkbook.Name,Nbr),"")
End Function

This function does not work properly , because when i close spreadsheet
, change it name , and then reopen , this function still returns the
same value . Any ideas how to solve this problem without writing
Workbook_Open() or Workbook_BeforeClose() events ?
 
Mon, 19 Jul 2004 16:08:46 -0500, Don Guillett wrote :
add
application.volatile
as the first line of your macro
Click to expand...

No matter if i try

Application.Volatile

or

Application.Volatile True

or

Application.Volatile Volatile:=True



it still doesn't work
As before i have to retype function name in the cell or
hit <CTRL> + <ALT> + <F9> to get proper results


I use exel 97
 
Your function calculated ok for me when I opened the workbook. But I'm using
xl2002.

Maybe you could add this to your workbook_open event:

Option Explicit
Private Sub Workbook_Open()
Application.Calculate
'or if that doesn't work:
'Application.SendKeys "%^{F9}"
End Sub
 
I put both of these in a regular module and both worked.

Function FileNm(Optional Nbr As Integer = 4)'yours
Application.Volatile
FileNm = WorksheetFunction.Substitute(ThisWorkbook.Name,
Right(ThisWorkbook.Name, Nbr), "")
End Function

Function fn()'mine
Application.Volatile
fn = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
End Function
 
Back
Top