Using Macros in Excel

[broadley]

I don't use macros, but recently I downloaded a macro from the CD attached to a computer magazine for use with MS Excel. My problem is that I can’t see how I make it available to all workbooks (new and existing). The Excel Help files just don’t seem to help. I’d like to access it from a toolbar button, but when I try to do this it just doesn’t work, and tells me that it can’t find the macro. It seems such a simple issue, but it’s got me foxed. What am I doing wrong?

[oldhermit]

Macros that run from a toolbar button will have an install program for the macro. If not that, then it should at least have some instructions on how to implement it.
If you can read the code of the macro, then it can be installed and set to run from a HOTKEY combination such as CTRL+R, or some such combination that you choose.
Typically a macro will look like this:

sub MacroName(<params>)
    
    several lines of
    macro code

end sub

Microsoft also has this article with step-by-step instructions on how to implement a macro. It includes a section for running a macro from a toolbar button. It is written for Word, but it works the same in Excel.
http://msdn.microsoft.com/library/de...ner_macros.asp
I just use hotkeys.

[broadley]

Thanks. I found the macro code and instructions. They say, “To use this code in your own macros, you will need to copy the macro code from this worksheet's Module1 in the VB Editor and paste it into a module in your personal.xls file. The sheet colorIndex sets out the 56 colors that you can use for the text and their colorindex number. For the background of the cells use the colour's RGB value.”

I haven’t given the macro code in this reply, but it’s purpose is to format all of the comments on a worksheet so that the comments box is sized to the exact size needed to show all the text in it, on a background colour. I’ve never used the VB Editor, and the Help files might just as well be in Swahili. I’d be none the wiser! Can you give me a step by step list of steps to take so that the macro is available on all worksheets? Thanks.

[oldhermit]

Hi broadley,
This seems to be a continuing concern, so I placed some step-by-step instructions for implementing Excel macros in the Academy Forum here.
http://discussions.virtualdr.com/sho...d.php?t=186488

If these instructions don't get your macro up and running, then post back and we'll see what else can be done.
Your right about the VB Editor.
It is programmed to read and correct code, so it doesn't work like a regular text editor. Trying to type anything extra into it, other than pasting the code, can be similar to getting stuck in a room with no doorway out!

[broadley]

I followed the instructions in the Academy Forum but hit problems. The macro doesn’t work so I presume I’ve done something wrong. On this assumption I tried to delete it and try again but I’m not able to delete it. I get a message saying, “Cannot delete a macro on a hidden workbook. Unhide the workbook using the Unhide command.” I haven’t got any hidden workbooks to my knowledge so I don’t know what this means. I do have some workbooks protected by passwords, and a couple of hidden sheets.
The info and help sheet of the macro reads:

"PC User Macro to format comments on a worksheet

This month's macro formats all comments on a worksheet to your prescribed format. We've used Verdana font 10 point size and navy type with a pale blue background.

This is the text of the macro:

Sub formatComments()
Dim UserComment As Comment
For Each UserComment In ActiveSheet.Comments
With UserComment.Shape.TextFrame
.Characters.Font.Name = "Verdana"
.Characters.Font.Size = 10
.AutoSize = True
.Characters.Font.ColorIndex = 11
End With
With UserComment.Shape.Fill
.ForeColor.RGB = RGB(171, 235, 234)
.Visible = msoTrue
.Solid
End With
Next
End Sub

Using this in your own macros
To use this code in your own macros, you will need to copy the macro code from this worksheet's Module1 in the VB Editor and paste it into a module in your personal.xls file.

The sheet colorIndex sets out the 56 colors that you can use for the text and their colorindex number. For the background of the cells use the colour's RGB value."

This is what I did:
Step 1. This was OK.
Step 2. The first line of macro code says, “Sub formatComments()”. I entered “formatComments” (without the quotes) as the macro name. Was it right to leave out the first word “Sub”? When I include it I get “Invalid name”. I used Ctrl+i as the shortcut key.
I selected “Personal Macro Workbook” to store the macro in as I want to use it in all workbooks. Is this right?
Step 3. This was OK.
Step 4. When highliting the macro code I left out the first line , “Sub formatComments” and the last line “End Sub”.
Steps 5-8. Seemed to be OK.
Step 9. I pressed Ctrl+i but nothing happened.
When I closed Excel I was asked if I wanted to save the changes to the Personal Macro Workbook and if so they'd become effective after restarting Excel.
Sorry this is so long, but you being able to see the macro might be useful. Your help is appreciated. Thanks.

[oldhermit]

Hi broadley,
The macro is fine.
It formats the text and puts color in the comments.
Apparently the Personal Macro Workbook is a Microsoft provision, which gets some good comments and some bad.
In all the time I have used Excel, that file has never been placed on my computer.
However, it serves as a central place for macros, so as you mentioned, after saving the file you should be able to open a NEW workbook and place a couple of comments on Sheet1 somewhere, and then run the macro.
If that still doesn't work, then try it again in a NEW workbook, and specify "This Workbook", and then save the file.
Chances are the Personal Workbook has some additional formatting that it is placing in all NEW workbooks.
Probably the same reason why people tend to delete the Personal Workbook, and just save each worksheet separately, like I do.

Otherwise, you did everything correctly, and it should run for you.
I appreciate your well-written explanation.
Here's how the code appears for optimal reading.

Sub formatComments()
'
' formatComments Macro
' Macro recorded 4/25/2005
'
' Keyboard Shortcut: Ctrl+i
'
Dim UserComment As Comment
    For Each UserComment In ActiveSheet.Comments
        With UserComment.Shape.TextFrame
            .Characters.Font.Name = "Verdana"
            .Characters.Font.Size = 10
            .AutoSize = True
            .Characters.Font.ColorIndex = 11
        End With
        With UserComment.Shape.Fill
            .ForeColor.RGB = RGB(171, 235, 234)
            .Visible = msoTrue
            .Solid
        End With
    Next
End Sub

[oldhermit]

Checking deeper into using the Personal Macro Workbook, you may need to reference these macros using the RUN button from the Macro dialog, where it is listed as being in the Personal workbook.
There are other means available for storing your macros, such as an ADD-IN workbook (one currently showing in the ExcelTip Programming forum) dedicated to storing macros and the toolbar icons that run them.
And there are also third-party programs that claim to provide more control over stashing macros in this way.
Even though there are many posts that show up in the various Excel forums having complications with these storage files, some people seem to have them under satisfactory control.
I just avoid the possibilities of having untimely delays and complications by using this method that is appearing to be somewhat problematic.

[broadley]

Thanks. I tried your suggestions - using a new workbook, then from the macro dialogue box using all 3 options (All Open Workbooks, This Workbook, & Personal.xls) but none worked.
I’m totally out of my depth in this stuff, thinking in my ignorance that there was a simple answer! I’ve come to the conclusion that it will be simpler and quicker to do the job manually, to say nothing of preserving my sanity! I don’t like giving up, but have to admit that Microsoft have got the better of me! Whatever happened to the old days of DOS when macros were simple to do?
So I’ll leave it until some other time when Microsoft make the process simple for the uninitiated like me. Anyway many thanks for your help, I much appreciate it.

[oldhermit]

That's really unfortunate, and suspect that this condition leads right back to interference being caused by the Personal Macro Workbook.
But it wouldn't be the first provision designed to save time that turned out to be a hindrance rather than having some benefit.
This may cause problems with every new template until the personal workbook is finally disabled, deleted, or whatever they do with them.
There should be some documentation on this, so will post back if something is found.
BTW Any spreadsheet can be saved as an Excel template, which is the method I have always used. Even though I have many macros, not all of them are included in a template. I keep many of them in a text file, and copy & paste as they are needed.
Having more than one template for Word and Excel is the norm for me, and NONE of them are saved to Word's Normal Dot or Excel's Personal Workbook. I specify that they always go into THIS document/workbook, and as it turns out, many of these documents are never saved at all, instead copying the macro to a text file, and then deleting the Document/worksheet.
Some are saved as regular documents, and some are saved as templates, which then appear with all the built-in templates each time I start an Office program. All I need to do is choose the template I want to use.
Not so complicated, and this method has certainly proven it's reliability.

[oldhermit]

Excel probably has more websites dedicated to it than any other program.
Still couldn't find a COMPLETE tutorial on the Personal xls, but the following threads will show that it can be disabled or removed, and even deleted and refreshed with a new one.

Stop Personal.xls from opening when starting Excel
http://www.mrexcel.com/archive/General/16757.html

It appears the Personal.xls can be moved so that it is not referenced.
The first answer is probably legitimate.
The second probably didn't understand the question.
http://www.mrexcel.com/archive/General/7825.html

This thread may reveal more than intended regarding the Personal Macro Workbook.
http://www.mrexcel.com/archive/General/19800.html

So as not to omit the possibility of a macro virus, here is info on one that affects the Personal xls, and has been around for awhile.
http://www1.umn.edu/oit/newsletter/0...acrovirus.html

May apply if you still wish to use Personal.xls
http://www.mrexcel.com/archive/General/9750.html

Incidental, since it shows Personal.xls can be deleted
http://www.adminlife.com/247referenc...28/144471.aspx

More...
http://www.adminlife.com/247referenc...26/130926.aspx

[broadley]

This Personal Macro Workbook and Personal.xls stuff really confuses me. Are these the same thing or 2 separate things? If I could work out what they do it would be helpful.
I'll follow up on the websites you've listed. Perhaps they will shed some much needed light on my confused brain!
I am familiar with templates and use them in Word, although in Excel I tend to copy/paste from a previous worksheet according to the need and then edit the worksheet until I get what I want.

[oldhermit]

Microsoft calls it the Personal Macro Workbook.
Other names used for it are Personal Workbook and Personal.xls
They are all the same thing.
From what I have found on the Internet about it, apparently it can be un-hidden and then closed, then either moved away from the XLStart folder, or just deleted.

While the Personal xls is un-hidden, the macros it contains can be highlighted, copied and moved to Notepad.
Then delete the Personal Workbook.
That's what I would do anyway, because if it won't run this macro, then there is likely to be something wrong with all the NEW spreadsheets created by it.
The method you described regarding your spreadsheets sounds reasonable to me.
The Personal.xls is not really necessary, but it can be restored whenever recording a new macro.

BTW working with macros may seem unnecessarily complicated while learning how to use them. But it's just a temporary condition because as you chug slowly along getting all the little particulars sorted out, after a while you find yourself moving at full speed.
It's a typical condition of computing (marriage, life, whatever...). But that's another story, and I'm no expert at that either...