Application.Volatile not working

[Jas]

Hi All,
I have the following macro that concatenates a range of cells, but it doesnt
auto-caculate. I have put Application.Volatile into the code but it still
doesnt work. Can someone help? (The macro is contained within a normal
module). I have also tried changing the function to "Public Function" and
that doesnt seem to work either...


Function Concat(myRange As Range, Optional myDelimiter As String)

Application.Volatile

Dim r As Range

For Each r In myRange
Concat = Concat & r & myDelimiter
Next r
If Len(myDelimiter) > 0 Then
Concat = Left(Concat, Len(Concat) - Len(myDelimiter))
End If

End Function



Any advice would be appreciated!
Thanks

 

[Jas]

Really sorry for wasting anyone's time, but the spreadsheet calculation was
set to manual. I have changed this to automatic and this works fine.

 

[Jim Thomlinson]

You don't need Application.volatile in this code and you should probably
remove it to save on overhead. By including the range as a parameter XL will
trach changes to the percident cells and recalc this formula as necessary...

 

[Jas]

Handy tip - thanks Jim

You don't need Application.volatile in this code and you should probably
remove it to save on overhead. By including the range as a parameter XL will
trach changes to the percident cells and recalc this formula as necessary...

 

[Bill Renaud]

The line:

Concat = Concat & r & myDelimiter

is calling the function recursively (with no arguments!). VBA allows
this type of statement, but I think it is generally better programming
practice to declare a local variable to use to concantenate the strings
together, then assign to the function at the end of the routine, like
so:

Function Concat(myRange As Range, Optional myDelimiter As String)
Dim strTemp As String
Dim r As Range

strTemp = ""
For Each r In myRange
strTemp = strTemp & r & myDelimiter
Next r

If Len(myDelimiter) > 0 Then
Concat = Left(strTemp, Len(strTemp) - Len(myDelimiter))
End If
End Function