You need to define the addresses as a range something like this...

with sheets("Sheet1")
tmpName = Left(.Range("B1"), 3) & " " & Right(.Range("B1"), 2)
end with
MsgBox tmpName

As a complete aside you should have Option Explicit at the top of your
module. Oddly enough it would catch this error for you as you would end up
with undeclared varaibles that would not compile... If you have more
questions about that just ask...
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

What is the VBA routine that is the equivalent to the formula;

= Left(B1, 3) & " " & Right(B1, 2)

When I use:

tmpName = Left(B1, 3) & " " & Right(B1, 2)
MsgBox tmpName

In works without an error, but the message box is empty?

As always, any help is appreciated.

Regards
Ronbo

I do see the grurs and some in particular, including you, suggesting the
"Option Explicit".

I have never taken the time to figure out why or how. Should I?

You should ALWAYS have Option Explicit at the top of your code module. There
is absolutely NO excuse not to.

Option Explicit forces you to declare all your variables using the Dim
statement. If you attempt to access a variable that is not previously
declared, you'll get a compiler error.

Without Option Explicit, VBA will declare variables as they are encountered
in code. Thus, if you misspell a variable name, VBA will create a new
variable with that misspelled name and assign it a default value of Empty
(which is interpreted as 0, vbNullString, or Nothing, depending on the
context). For example, suppose you're writing a large application that does
something with printing, and you use a variable named "PageCount". VBA will
create a variable by that name and give it a default value. A thousand lines
later, you use "PageCounter" instead of "PageCount". VBA will create a
variable named "PageCounter" and give it a default value. But that isn't the
value you expect. You expect the value in "PageCount", not "PageCounter".
This will likely cause a logic problem or cause a runtime error, depending
on what you are doing in the code.

Debugging errors in code that does not use Option Explicit is a nightmare.
In the example above, if someone else wrote the code and it is up to you to
debug it, how do determine whether there is supposed to be a single variable
named "PageCount" and "PageCounter" is a mistake, or whether the original
programmer really did intend for there to be two separate variables? You
really can't tell. You have go line-by-line looking for undeclared
variables. Debugging becomes nearly impossible.

Also, if you don't declare your variables, VBA declares them As Variant.
Variant variables add considerable overhead to the processing because behind
the scenes, VBA must execute code to determine what type of value is stored
within the Variant and then do type conversions to get the value out of the
Variant. Variants should be used sparingly, only when absolutely necessary.

In summary, ALWAYS use Option Explicit.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Ronbo" wrote in message
...
Jim -

I did not see your writting on "Option Explicit" until I re-read. Your
programming worked perfect and I did not continue to read.

I do see the grurs and some in particular, including you, suggesting the
"Option Explicit".

I have never taken the time to figure out why or how. Should I?


"Jim Thomlinson" wrote:

You need to define the addresses as a range something like this...

with sheets("Sheet1")
tmpName = Left(.Range("B1"), 3) & " " & Right(.Range("B1"), 2)
end with
MsgBox tmpName

As a complete aside you should have Option Explicit at the top of your
module. Oddly enough it would catch this error for you as you would end
up
with undeclared varaibles that would not compile... If you have more
questions about that just ask...
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

What is the VBA routine that is the equivalent to the formula;

= Left(B1, 3) & " " & Right(B1, 2)

When I use:

tmpName = Left(B1, 3) & " " & Right(B1, 2)
MsgBox tmpName

In works without an error, but the message box is empty?

As always, any help is appreciated.

Regards
Ronbo