Why does my Function not work?

[bjdesa]

in VBA Excel
Function GetText() As String
Dim Str1 As String
For Row = 51 To 74
Str1 = Str1 & Cells(Row, 3).Text
Next Row
GetText = Str1
End Function

in worksheet
cell C77 contains =gettext()

Now when any row 51 to 74 and column 3 changes my function gettext
does not update. Could some explain to me why and what I should do to
fix it

Thanks,

 

[JE McGimpsey]

Since you don't have the range in your function's arguments, XL has no
way of knowing that cells C51 or C74 have anything to do with your
function.

You could force the function to calculate every time the sheet
calculates by including Application.Volatile at the top of your macro.

Better, you could put the range in your function's arguments:

Public Function GetText(byRef rng As Excel.Range) As String
Dim rCell As Range
Dim sTemp As String

For Each rCell in rng
sTemp = sTemp & rCell.Text
Next rCell
GetText = sTemp
End Function

Then

C77: =GetText(C51:C74)

 

[Gord Dibben]

Make it volatile.

Function GetText() As String
Application.Volatile True
Dim Str1 As String
For Row = 51 To 74
Str1 = Str1 & Cells(Row, 3).Text
Next Row
GetText = Str1
End Function


Gord Dibben MS Excel MVP