Curious use of VBA Mid() function

[Richard Schollar]

Not a question - just sharing an interesting use of the VBA Mid() function.

Did you know you can use Mid to assign a replacement string of the same size into a variable:

Dim s As String
s = "True fact: Fred is a hero!"
Mid(s,Instr(1,s,"Fred",vbBinaryCompare)) = "Rich"
MsgBox s
'... Produces:
'... True fact: Rich is a hero!

The code in red is an assignment not a comparison! Looks utterly crazy to me. Not sure this is much (if any) use in the real world, but is is facinating!

 

[Scott Huish]

I have used this, but not very often.

But in playing with it today, I found that the replacement string can be longer than what you actually use, it will use the number of characters specified in the MID statement.

Sub test2()
Dim t As String, r As String
t = "This is test"
r = "a fun"
Mid(t, 6, 2) = r
MsgBox t
End Sub