Very basic question... I MUST be having a brain fart....

[Phillips]

I want to change the content of a string with a user defined function

example:
Cell A1 contains "[firstname], on [date], your order was shipped. [exit1]"
Cell A7 contains "[firstname], Thanks for your order."
ActiveCell.Offset(0, 1) = "Bob"
ActiveCell.Offset(0, 2) = "11/25/2003"

I want to get back "Bob, on 11/25/2003, your order was shipped. Bob, Thanks
for your order."

When I try the below, I get an empty result. When I put a msgbox at the
beginning and the end of the function, it has what I would expect it to, but
nothing gets returned...

How do I pass the value to and get a value from a user defined function????
Where in the helpfile can I find this info?

in my code, I would like to have something like:
stringToExpand = ThisWorkbook.Sheets("Bioler").Range("A1").Value
response = boilerplate( stringToExpand)
msgbox response
--------------------------
function boilerplate
tester = 0
Do While InStr(oldscript, "[") > 0
boilerplate = Replace(boilerplate, "[firstname]", ActiveCell.Offset(0, 1))
boilerplate= Replace(boilerplate, "[date]", ActiveCell.Offset(0, 2)))
boilerplate = Replace(boilerplate, "[exit1]",
ThisWorkbook.Sheets("Bioler").Range("A7").Value)
tester = tester + 1
If tester > 99 Then
Exit Do
End If
Loop
End Function

 

[Jim Rech]

UDFs cannot affect other cells. All they can do is return a result to the
cell they are in, just like Excel worksheet functions.