Count characters selected in code in the VBA editor

[Gunblade]

Hi there,

Can't google this because all it gives is a formula to count characters in excel which is not what I'm looking for.
I need to count the actual number of highlighted characters in a vba formula because the Evaluate function only takes an archaic 255 characters.
Is there any option to turn this on in the code editor?

Thanks.

[6StringJazzer]

I don't know of a way to do this directly in the VBA editor. The Standard toolbar has a box that shows the line number and column number of the cursor, so if your formula is on one line you can get the beginning and ending column numbers and subtract.

A workaround is to do it in Word. You can copy the formula and paste into Word. Right click on the status bar and select Character Count. The count of the selected characters appears in the status bar.

[Gunblade]

A shame but thanks for responding. I was hoping I didn't have to go through the cut and paste into word :P
I could of course hope for Microsoft to remove the 255 cap.....

Maybe a better chance of buying a winning lottery ticket...

[LJMetzger]

Hi,

You can 'cut and paste' into the Immediate Window to get the length.

For example try the following:
a. Open the Immediate Window (Ctrl G)
b. Select the text.
c. Type the following in the Immediate Window: s = "
d. Paste the selected text after the 'double quote". A final double quote will automatically be added.
e. In the Immediate Window type:
?len(s) followed by the Enter key

Lewis

[romperstomper]

You can use code like this (assumes you've only selected text on one line):

Please Login or Register  to view this content.

[LJMetzger]

I believe there are 2 prerequisite' for rorya's code to run:

a. In Excel Trust Center, CheckBox 'Trust access to the VBA project access model' MUST be selected.

b. In VBA > Tools > References > Enable 'Microsoft Visual Basic For Applications Extensibility 5.3'

Lewis

[romperstomper]

a) is correct (I should have mentioned that, so thanks!) but there is no need for the VBA extensibility reference.

[Haluk]

Based on Rorya's code in message #5, maybe this;

Note: As stated in message #6; in Excel Trust Center "Trust access to the VBA project access model" must be selected and the reference "Microsoft Visual Basic For Applications Extensibility 5.3" must be added to the project.

Please Login or Register  to view this content.

[6StringJazzer]

You can use code like this (assumes you've only selected text on one line):

From now on I am never going to say there is no way to do something no matter how certain I am or no matter how bizarre it sounds.

[Gunblade]

Thanks for the ideas, I'll take a look at it thanks