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

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.

"Jas" wrote:

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

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...
--
HTH...

Jim Thomlinson


"Jas" wrote:

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.

"Jas" wrote:

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

Handy tip - thanks Jim

"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"Jas" wrote:

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.

"Jas" wrote:

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

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

--
Regards,
Bill Renaud