NestMeUp and SpaceMeOut - Excel 2000

[ChrisGreaves]

The attached text file contains a string copied from an Excel cell; only the names of variables have been changed to protect the innocent.

The macros NestMeUp and SpaceMeOut are meant to be pasted into a Word module.

I paste an offensive cell formula into a blank Word document and run SpaceMeOut to insert spaces to make the formula readable.

I paste an offensive cell formula into a blank Word document and run NestMeUp to assign colors to different levels of nesting to make the formula readable.

[HansV]

Hi Chris,

In Office 2000 and later, VBA has a built-in function Replace that works the same as strReplaceAll, but faster.

[ChrisGreaves]

... built-in function Replace that works the same as strReplaceAll, but faster.

That's all very well for you to say, Hans, but right now I'm being paid hourly-rate to work from home.

(Thanks anyway!)
I guess I wrote strReplaceAll back in Word97 days and just got used to it.
I'll drag myself, kicking and screaming, into the New Millennium next time around, I promise.

[HansV]

The macros are interesting, but I found one problem with SpaceMeOut: underscores are allowed in defined names in Excel, e.g. January_Sales. The SpaceMeOut macro will change this to January _ Sales, which is not a valid name any more since it contains spaces.

[ChrisGreaves]

underscores are allowed

Thanks for catching this, Hans.
As you might guess these two were "quickies" dashed off this afternoon to solve a specific and recurring problem.
The fix will be:
Public Const strcAlpha As String = strcLowerAlpha & strcUpperAlpha & "_"