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