VBA string funcs on left side of assignment?

[curiousgeorge408]

Empirically I learned that the VBA function Mid() can be used on the
left side of an assignment. For example:

s = "12345678"
Mid(s, 4, 2) = "cd"

results in "123cd678". But the following does not work:

Left(s, 2) = "ab"
Right(s,2) = "ef"

Then I looked at the Help text for Mid, and I found nothing that
indicates that even Mid() can be on the left side of an assignment.

Where is written that I can use Mid() on the left side?

Is there something else that I should use to overwrite any part of a
string?

(Other than cumbersome concatenation like Mid(s,1,3) & "cd" & Mid(s,
6,3).)

Is there an easier way to overwrite the left and right parts of a
string other than using Mid()?

 

[Doug Glancy]

That is interesting.

When you press F1 over the word "Mid" in XL2003 it gives a choice between
the Mid function and the Mid statement. It's the statement that allows you
to replace, and there is no analogous Left or Right statement (only
functions).

For overwriting the left part I guess I'd use:
s = "12345678"
s = "abc" & Right(s, Len(s) - 3)

or something like that.

hth,

Doug

 

[Rick Rothstein \(MVP - VB\)]

When you use Mid on the left side of an assignment (you cannot do this with
Left or Right), you are not using the function form of Mid, rather, you are
using the statement form of it. In the VB editor's search field, type "mid
statement" followed by Enter and you will get one link to click on... that
is the one you asked about.

Rick

 

[Rick Rothstein \(MVP - VB\)]

No, to replace characters on the left, you would set the start value at one,
like this...

S = "12345678"
Mid(S, 1, 3) = "ABC"

To replace on the right, you would do it this way...

S = "12345678"
Mid(1, Len(S) - 2, 3) = "ABC"

By the way, it is -2 (not -3) because we are subtracting to produce a
counting position, hence the +1 comes into play (LenS - LenR + 1).

As an aside, using Mid as a statement is a very, very fast way to replace
characters in a piece of Text... much, much faster than using concatenation.
This speed advantage doesn't come into play if you are just using it once or
twice; but, in a large loop, the time difference can be quite substantial.

One final note... if the number of characters that you are going to replace
is exactly equal to the length of the replacement text on the right, you do
not need to specify the length parameter of the Mid statement... if you omit
the Length statement, then all character in the replacement text will be
assigned. There is no difference between doing this...

S = "12345678"
Mid(1,2, 3) = "ABC"

and this...

S = "12345678"
Mid(1, 2) = "ABC"

because we want the all of ABC to replace 234 in the source string. However,
there is a big difference between doing this...

S = "12345678"
Mid(1,2, 3) = "ABCDEF"

and this...

S = "12345678"
Mid(1, 2) = "ABCDEF"

because we only want **part** of the replacement string (its first 3
characters) so now the length argument is very important.

Rick

 

[Doug Glancy]

Thanks Rick!

Doug

No, to replace characters on the left, you would set the start value at
one, like this...

S = "12345678"
Mid(S, 1, 3) = "ABC"

To replace on the right, you would do it this way...

S = "12345678"
Mid(1, Len(S) - 2, 3) = "ABC"

By the way, it is -2 (not -3) because we are subtracting to produce a
counting position, hence the +1 comes into play (LenS - LenR + 1).

As an aside, using Mid as a statement is a very, very fast way to replace
characters in a piece of Text... much, much faster than using
concatenation. This speed advantage doesn't come into play if you are just
using it once or twice; but, in a large loop, the time difference can be
quite substantial.

One final note... if the number of characters that you are going to
replace is exactly equal to the length of the replacement text on the
right, you do not need to specify the length parameter of the Mid
statement... if you omit the Length statement, then all character in the
replacement text will be assigned. There is no difference between doing
this...

S = "12345678"
Mid(1,2, 3) = "ABC"

and this...

S = "12345678"
Mid(1, 2) = "ABC"

because we want the all of ABC to replace 234 in the source string.
However, there is a big difference between doing this...

S = "12345678"
Mid(1,2, 3) = "ABCDEF"

and this...

S = "12345678"
Mid(1, 2) = "ABCDEF"

because we only want **part** of the replacement string (its first 3
characters) so now the length argument is very important.

Rick

 

[curiousgeorge408]

When you press F1 over the word "Mid" in XL2003 it gives
a choice between the Mid function and the Mid statement.

Ding! I must've stumbled across the Mid Statement help text some time
ago, but I wasn't so lucky recently. Shoulda just typed "Mid" in the
search field.

Anyway, thanks for the pointer.