Excel - how to update/refresh cells when minor change made to a string value in a VBA function

[essaytee]

I'm working on an Excel sheet, have created many functions that return strings to the cell(s). After applying all the VBA functions to the cells (1,000's), if I make a slight change to a string within a VBA function that obviously changes the return string to the cell, how can I automatically refresh/update all the cells?

Clicking the 'Calculate Now' or 'Calculate Sheet' buttons does not work for VBA functions.

My work-around so far is to re-enter the functions, and dragging them down the rows, surely there is an easier way.

Is there an easier way?

 

[cheekybuddha]

Can you post here a 'before' and 'after' of a VBA function that you change so we can understand exactly what you mean, and also an example of a cell formula containing the function.

I ask because it's hard to imagine why you would need to update the cell references at all - you'd think a change within the function would update the cell values automatically

 

[essaytee]

In a cell I have the following: It's a simple example.

=WriteExhibit(A5, A6, A7)

Function WriteExhibit(strDoc as string, datDate as Date, strRequestedBy as string) as string
   Dim strReturn as string
   strReturn = "The following document is: " & strDoc & " created on " & datDate & " and requested by " & strRequestedBy
   WriteExhibit = strReturn

End Function

The function works as intended. I apply it to 100's of rows, all correct.

So when I modify the function by changing a hardcoded part of the string from:

strReturn = "The following document is: " & ........

to:

strReturn = "The next document is: & ........

The cells are not updated.

 

[Edgar_]

Just a thought... are you perhaps changing only the code and not triggering any change on the worksheet?

 

[essaytee]

Just a thought... are you perhaps changing only the code and not triggering any change on the worksheet?

The returned string will be different, so yes, the cell data should change.

 

[Edgar_]

I made a simple test with 3k rows and after clicking Recalculate it does recalculate, it takes a while but the status bar shows the process.

Does the status bar show you anything?

 

[arnelgp]

you can use Application.Volatile True, so you can Recalculate (F9 function key).

Function WriteExhibit(strDoc as string, datDate as Date, strRequestedBy as string) as string
   Dim strReturn as string
   Application.Volatile True
   strReturn = "The following document is: " & strDoc & " created on " & datDate & " and requested by " & strRequestedBy
   WriteExhibit = strReturn
End Function

 

[cheekybuddha]

you can use Application.Volatile True, so you can Recalculate (F9 function key).

I found that Application.Volatile True was not necessary for F9/Calculate Sheet to refresh the display. (perhaps unless it had previously been set to False)

 

[arnelgp]

I found that Application.Volatile True was not necessary for F9/Calculate Sheet to refresh the display.

Clicking the 'Calculate Now' or 'Calculate Sheet' buttons does not work for VBA functions.

 

[Isaac]

Make any change on the worksheet after you make change to the code and make sure that volatile is set to true. Be aware that volatility combined with udfs on worksheets can be a little glitchy and there are many downsides to using them.

Oftentimes I am in favor of using worksheet change code instead unless you have tens or hundreds of possibilities

 

[cheekybuddha]

essaytee said:
Clicking the 'Calculate Now' or 'Calculate Sheet' buttons does not work for VBA functions

They work in my (and @Edgar_ 's) testing, so perhaps volatility has been disabled somewhere else?

 

[essaytee]

Sorry for my absence, anyway I'm back. Where is the Volitile function/button?

 

[Isaac]

Sorry for my absence, anyway I'm back. Where is the Volitile function/button?

It's a statement that may or may not be contained inside your UDF.