Macro adjustment please!

[Guest]

Hi - and thanks for reading this.

I have 'purloined' the following macro, posted years ago by Evelyn (thanks
to her). It is to replicate the Excel Substitute function in Access. The
problem I have is that if the strInPhrase in blank, Access gives me #Error.
If anyone could tell me how to overcome this error, I'd really appreciate
it.
Thanks.

Public Function Subst(strInPhrase As String, strSubstThis As String,
strWithThis As String)
'given a phrase (strInPhrase), this function will search for
'strSubstThis in the phrase and replace it with strWithThis
'it returns the change phrase
'Ev woolston
Dim FindInStr As Integer
Dim PartBefore As String
Dim PartAfter As String
Dim NewString As String
Dim LenSubstThis As Integer
Dim LenParts As Integer
LenSubstThis = Len(strSubstThis)
CheckIfThere:
FindInStr = InStr(1, strInPhrase, strSubstThis)
If FindInStr = 0 Then
'find strSubstThis in strInPhrase
'if it doesn't exist there then
'return the entire phrase unchanged
GoTo Unchanged
End If
'get part of message before strSubstThis
PartBefore = Left$(strInPhrase, FindInStr - 1)
LenParts = Len(PartBefore) + LenSubstThis + 1
PartAfter = Mid(strInPhrase, LenParts)
NewString = PartBefore & strWithThis & PartAfter
'check new string for a further
'occurance of strSubstThis
strInPhrase = NewString
GoTo CheckIfThere
Unchanged:
Subst = strInPhrase
End Function

 

[Ken Snell \(MVP\)]

Why not just use the Replace function that is built into ACCESS VBA:

NewString = Replace(OldString, "OldText", "NewText")

 

[Guest]

Hi Ken

Thanks for your response. I didn't know there was a replace function -
that's why!
I still get the same error with replace, though.

Andy.

 

[John Spencer]

The error is coming because your arguments are all defined as strings.
That means that cannot accept null values.

One fix is to change the arguments to accept nulls by making them variants.

Public Function Subst(strInPhrase as Variant, _
strSubstThis as Variant, _
strWithThis as Variant)

IF IsNull(strInPhrase) _
or IsNull(StrSubstThis) or _
IsNull(strWithThis) Then
Subst = StrInPhrase

ELSE
Subst = Replace(strInPhrase,strSubstThis,strWithThis)

End If


Another fix is to test the values before you pass them to the Function
or concatenate a zero-lenth string onto each value to ensure it is a
string. Something like the following where X and Y are variables that
could be null.

Subst([SomeField] & "", X & "", Y & "")


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================