Advice on Excel VBA + Macros

[Johnn]

Hello,

I am good at excel but have not written any macros - and in order to update my skills on macros I have seen a book on the net - 'Excel VBA & Macros with Mr Excel' however I am not sure of the following:

1. I have the office suite 2000 - and I am not sure if the above book+CD will be compatible with my version of office (my OS is XP)?

2. Will the above book be a gook tool for me to understand and write macros?

If not can any one recommend which books+CD would be compatible with office 2000.

Many Thanks

 

[SydneyGeek]

1: Yes
2: Yes

That book covers versions up to 2003. Go to a bookshop, browse through it and have a look at the style. If it works for you, buy it. A good alternative is John Walkenbach's Power Programming book(s). Get one for 2003 -- you will have trouble getting one for earler versions than that.

And if you like the MrExcel book you can buy it from the online store on this site; also have a look at some of Bill's more recent books.

Denis

 

[Johnn]

Denis,

Thanks for your advice!

I'll have a look into both the books that you have mentioned before I buy one of them.

Once again thanks very much

Johnn

 

[chuf]

I too am just starting to get a grip with VBA .. and i wondered about general administration issues for your code.

I'd like advice on two things please.

1. Do you keep all your codes in your personal.xls or do you have a master spreadsheet with all codes or do you "just" keep them in the workbook / worksheet in which they are used ? or another ?

2. If i'm creating a code for a number of users and the process will be - newly created spreadsheet from IT system -> Spreadsheet with VBA to change format -> feed into another IT in the desired format.

How would i best save the VBA code ? Do i provide each user with a spreadsheet which has the VBA code in the "this workbook" area . Then they can move the newly created spreadsheet from the IT to it ? or is there a better way ?

I'd be grateful for your views ? Thanks Chuf

 

[Johnn]

Chuf,

I am unable to help you as I am at the basic stage of VBA+ macros. It would be advisable if you posted your questions to the experts!

Johnn

 

[SydneyGeek]

For code that you want to use in that workbook, put it in a standard module (Insert > Module). It will be available to any open workbooks, as long as the workbook is open

For code that you want to use whenever you use Excel, put the code into your personal macro workbook.

For code that will be used by many users in a specific workbook, create an add-in for that workbook. When you need to change the code you change it in one place only; otherwise you need to manage the individual updating of many different workbooks, and you don't want to go there.

Don't put general-purpose code behind a worksheet. You can only use that code in that sheet, and if the sheet is deleted you will lose the code too. Sheet modules are used for code that triggers when something happens to that sheet, eg activate, calculate, change the selection, change one or more specific cells.

Hope that helps
Denis

 

[chuf]

Thanks Sydney ( i can't call you a Geek)

Yes, thanks really helpful. These "admin" things are so important to get right.

I'm reading Excel 2003 power programming ( good grief its heavy) but have yet to get to AddIns - certainly sounds like the way to go.

Thanks again.

 

[Derek Brown]

Don't forget to have a look at the description of the "Live Lessons Excel VBA & Macros with Mr Excel" DVD - this compliments the book very well.