Passing Target to a Function

[AlexanderBB]

Just found if the function changes this the worksheet Cell also changes.
I tried ByVal and ByRef but this had no effect.
What would be the correct way to handle this? Create another variable and use/change that ?

 

[Scott Huish]

Care to share your code and what your trying to accomplish exactly?

 

[AlexanderBB]

I'm building sql for a query sing a wrksheet value as criteria.
It's still at the development/testing stage but what I have is

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sTarget As String
If Target.Row > 1 Then
    Cancel = True
    Set iRange = Range("A" & Target.Row & ":CV" & Target.Row)
    sTarget = Cells(1, Target.Column)
    Select Case sTarget
       Case "Artist", "B-Side Artist"
            Call CEvents(Target)
    End Select
End If
End Sub
Sub CEvents(t)
t=checkquotes(Cstr(t))
end sub
Function QuoteCheck(i$) As String
'Remove trailing or leading quotes
'Change any enbedded quotes
    If i$ > "" Then
        If Right(i, 1) = "'" Or Right(i, 1) = Chr$(34) Then i = MyTruncate(i, 1)
        If Left(i, 1) = "'" Or Left(i, 1) = Chr$(34) Then i = Mid(i, 2)
        i$ = Replace(i, Chr$(145), "")
        i$ = Replace(i, Chr$(146), "")
        i$ = Replace(i, Chr$(147), "")
        i$ = Replace(i, Chr$(148), "")
        i$ = Replace(i, "'", "%")
        i = Replace(i, "[", "%"): i = Replace(i, "]", "%")
        i = Replace(i, Chr$(34), "%") 'embeded quotes
    End If
QuoteCheck = i$
End Function

So the target object is being changed and (probably correctly) changes the worksheet.
I changed CEvents to
Dim a As String
a = QuoteCheck(CStr(t))
And that may be good enough, but wondering if its the correct way.

 

[StephenCrump]

Rather than using a Sub to call a Function, why not more simply:

Target.Value=QuoteCheck(Target.Value)

 

[AlexanderBB]

Okay but that still changes the worksheet,

 

[StephenCrump]

If you want just the string value, then assign it to a string variable:

s = QuoteCheck(Target.Value)

If that's not the answer, then it's back to Scott's question ...

What are you trying to do here?

 

[AlexanderBB]

Thanks, got it - assign a new variable rather than change the target. I thought as much but wanted to be sure
there wasn't a way to use Target without it changing the worksheet. As in setting it read-only or something.
I'm changing old code that previously didn't use Target to now using it as it has all the info needed.
I am building sql for a query sing a worksheet value as criteria.