Application.Volatile not working
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 |
Application.Volatile not working
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 |
Application.Volatile not working
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 |
Application.Volatile not working
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 |
Application.Volatile not working
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 |
All times are GMT +1. The time now is 10:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com