Character Count in an Excel VBA Variable 1

[SaintAugustine]

Howdy guys,

I'm trying to...well, long story. I'm writing a macro that will rename a worksheet tab based on user input.

The problem is that the name of the worksheet combines a couple of variables - so the variable for the name sometimes exceeds 30 characters, but worksheet tabs can't be over 30 characters.

So I'm trying to write a subroutine that will count the number of characters in the variable which holds the worksheet name. Then, if the variable exceeds 30 characters, a dialog box will pop up and ask the user to input an abbreviation of the variable name.

The problem I'm having is, how can I tell Excel VBA to count the number of characters in a variable? I tried myVariableCount = NewSheetName.Characters.Count, but for some reason this doesn't work.

Any ideas? This is a lot of detail for a pretty simple question, sorry.

(for those of you who are thinking all the way through this - I'm going to put this in a loop - it will keep asking the user to abbreviate until the character count gets to less than 30)

 

[ttmac]

try

myVariablecount= len(myVariable)
if myVariable >30 then
prompt for abbrev
else
rename
end if

Tom

 

[SaintAugustine]

Geez - for some reason I thought there was a trick to using regular functions in VBA - thanks Tom!