Mr. Excel's Book

[Berrett]

I was just about to buy "VBA and Macros for Microsoft Excel", when I saw this review at Amazon.com:

[BEGIN QUOTE]
"It didn't take me long before I found the first error. Of course at first, I thought it might be the only one. I noted it down to send to the author later.

"Then I found another, and another. Finally the list got too long to send. And some of them were quite substantial, in my opinion!

"Some of these so-called "tips" will only work if you have a particular version of Excel running a particular version of Windows. They won't work for everyone. That will frustrate many readers."
[END QUOTE]

I use a Macintosh G4 Powerbook -- so I'm particularly worried about the "tips" not working on my version of Excel. Would someone mind commenting on this? Is the book good for all versions of Excel? Are there errors that are substantial like this guy claims?

Thanks -- Berrett

 

[starl]

The book was mainly geared toward the versions that run on PCs. Most of the code will work on the newer versions of Excel, but there are exceptions - and it's because MS didn't cover them for Macs. Examples - for sheet protection, userinterfaceonly won't work. Forms are a little limited in design because the Macs default system settings will overwrite the fonts. Also, ActiveX controls won't work - you'll have to use Form controls on your sheet.
Else, the book IS a good reference for anyone wanting to learn Excel VBA. Just keep in mind that Microsoft wrote the product for PCs, not Macs, so there are some things that won't work. But they are few and far between.

If you have any problems with the code samples (they aren't tips but examples - you'll notice if you read the book), post them here - we'll work them out and - hopefully - incorporate the workarounds in a future edition.

 

[Excelsiar]

Well, I have researched excel and VBA for a long time! I did buy the book today. When I bought it I knew that it would not be PERFECT. That would not be possible. What I did know was that it would do the job of getting me from where I was to where I wanted to be BETTER than any other product I have found so far.

I have a dozen or so books on excel. Some of them are over 1,000 pages! They don't do for me what I needed to have done. That is this: I am an avid computer user with thousands of hours on a PC and mostly on windows. I am considered by my peers at work as the excel guru ... though I consider myself to know so very little. I can use the basic tools well, but I haven't been exposed to any languages. In university, we had punch cards and Fortran. I ran from that. Now I can see that with some enhanced excel skill, I can do tons of things. Heck, at 49 I look forward to retiring from the Government job I have to work as a private consultant. This book will start me on the way.

The bottom line is, in my opinion, this book is targeted to people like me. I wish I had found it two years ago! As to the errors.... I don't care. It is a dynamic work, as I found out tonight.... with answers coming shortly after my first post. As a tool, the book gets top marks from me. It is simply the best I have found to date.
:wink:

 

[starl]

Thank you, Nick. It's *fantastic* hearing how the book is helping people.

 

[shades]

I just got the book over the weekend (great book!!!), and have started using it at home. I tried several of the functions on my Mac (eMac, OS X 10.3.6, XL2004), and so far, everyone has worked as advertised. I also use XL2002 at work, so will be putting them to use.

My goal is to try every example in the book on the Mac, just to see whether they are possible. Starl and I have discovered some areas where the Mac side VBA is sorely lacking (and to think that Excel started on the Mac).

 

[starl]

shades - if you could track what doesn't work, we'd really appreciate it. You can either post it here.. maybe make a new thread or create a doc and send it to me - I'll see if Bill might be interested in creating a webpage - especially if we can figure out the workarounds.

 

[shades]

VBA and Macros, Page26 Macro

One problem -- for both Windows and Macs -- is in ProjectFilesChapter01.xls, the Page26Macro.

When used with the worksheet, InvoicesTuesday(2), produces this error message (Mac a little more descriptive ) :

(on the Mac) Method 'OFFSET' not valid Object 'Range'

(on Win2K) Application-defined or object-defined error

The reason is because the cell selected prior to running the macro is not in the range. Thus, when it gets to the line:

    ActiveCell.Offset(1, 0).Range("A1").Select

It actually selects cell B65356. This seems to be related to omitting the portion related to the actual import portion of the macro. By following that portion of the import, the range is still automatically selected, and hence no problem. Evidently when the file was saved, cell B29 was selected, which is outside the range.

Two options to correct.

1. Be sure that prior to running the macro ensure that the selected cell is in Column A in the range to be used. If a cell in column B is selected, the result will move "Total" to B18, and each one to the right. If a cell in Column C is select, then the Total line is moved to the right two columns. Therefore, make sure that a cell between A1:A17 is selected.

2. This line to the code below the last line of explanation. So it would look like this:

    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select