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!

 

[Gunswick]

could you use it instead of application.substitute ?

i found that substitute fails if you attempt to sub the same value in as out (ie. a find n replace of the same word)

maybe this wouldnt fail etc

 

[Richard Schollar]

The restriction is that the replacement overwrites the original string from the position given by Mid (ie in my example above from the character in s where "Fred" is found). You can't use this method to change the length of the original string.

It just looks weird to me!

 

[VoG]

That's interesting Richard, thanks.

I had never thought of doing it that way, but I will

 

[RoryA]

Coincidentally I was reminded of that the other day as I was flicking through the VBA Developers Handbook. I did know it, but I don't think I have actually used it more than about twice. It's unfortunate that you can't do something like:

Mid(selection.formula, 2, 4) = "test"

for example.

 

[pgc01]

Hi

A remark, this is not the Mid function, it's the Mid statement, it's one of the string statements.

I must say that I've also only used it a few times. I think of these 3 together: LSet, Mid, RSet. These 3 string statements don't change the length of the string. They are effective and I like to know they're there, but I really only use them once a year.

 

[Richard Schollar]

Ah so it wasn't a case of a function behaving strangely then! That answers why it looks so wonky. I didn't realise there was a Mid statement until you said that (then confirmed it in VBA Help ).

I have used LSet before to parse fixed length records. I could see some use, perhaps, with using the Mid statement to amend a code for example. But in the end I expect I would use normal functions etc to benefit from easier readability.

 

[RoryA]

I've used LSet a few times to copy a UDT, but I don't think I've ever used RSet.

 

[Norie]

Interesting, but I don't think I'd use it too often.

I prefer Replace.

Dim s As String
s = "True fact: Fred is a hero!"
s = Replace(s, "Fred", "Rich")
MsgBox s

 

[pgc01]

Hi Norie

I prefer Replace.

This is not an alternative. You use the Mid() statement to replace characters at a specific position.

Ex: in "I prefer Replace.", change the "e" to "o" in position 5

s = "I prefer Replace."
Mid(s, 5, 1) = "o" ' using Mid() statement
 
s = "I prefer Replace."
s = Left(s, 4) & "o" & Mid(s, 6) ' alternative with string functions, messier
 
s = "I prefer Replace."
s = Replace(s, "e", "o") ' wrong, not an alternative