Monday, April 24, 2006

VBA tricks - capturing spreadsheet data in VBA

I have been writing and debuging a lot of VBA code lately, though unfortunately most of it is very specific to the applications it is for.

Here is a cool trick I use regularly to combine VBA with spreadsheets and manipulate them.

To use a value stored in a cell, eg a constant or variable, can be done simply by sucking that value into a VBA variable. Naming the range of that cell with the variale name makes this very easy and also not affected by most spreadsheet changes that a user can do. The main two things you need to watch for is the sheet that the range name is on is either hidden or deleted (renaming doesn't matter) or the cell itself deleted.
I also tend to follow some other principles.
Make sure that the range name contains no "illegal" characters. Most SHIFT and a number and punctuation symbols are not allowed in a range name.
Name the range and the variable in VBA slightly differently. I tend to use a convention of my own for this. Eg the range name will be my_name and the VBA variable will be myname.

Dim myname as string
myname = Range("my_name").Value

That piece of code will suck the value from the cell and store it in a variable.


Posted by: Jethro | 7:59 AM |

Friday, April 14, 2006

Introducing Office 2007 + PDF Handbook for Office

Following is a Excel Newsletter from Charley Kyd.

Introducing Office 2007 + PDF Handbook for Office


Charley Kyd
Wednesday, April 12, 2006 Just minutes ago, as I was preparing to send this message, I received a workbook from a reader in India. "I came across this interesting piece in which Excel has been used to draw a village scene in India," he wrote. "I thought it fit to share with you."I liked the workbook, even though ExcelUser is dedicated to the business use of Excel, and the workbook serves no business purpose that I can see. What the workbook does do is to illustrate (pun intentional) the wide variety of purposes that Excel can serve. If you're interested, you can download it here.

Introducing Excel 2007


If you would like to beta test Office 2007, you'll probably get your chance soon. Microsoft now is accepting Beta 2 registrations. To register, go to Key Microsoft Web Sources Explain Office 2007 Features and then follow the link to the right of my byline.
People have tended to look at recent upgrades of Excel and asked, "So what's changed?" You definitely won't say that -- and mean it -- when you see this version of Excel. The changes have been massive, and obvious. I can't talk yet about any of the changes to Excel based on my own knowledge or opinions. But theWeb Sources article provides information about some of the many changes that Microsoft has brought to Excel. And the article offers links to the key Microsoft web pages where you can learn much more about Excel 2007.
I begin the article with a short discussion of Excel's user interface (UI). As you can see from the first figure in the article, the UI has changed completely. We no longer have menus. We have the Ribbon.
The Ribbon probably will become one of the most widely discussed changes to Excel. In fact, I've just noticed that already it's more widely discussed on the web than I ever would have guessed. To see what I mean, use Google to search for: ribbon "excel 12" OR "excel 2007". Today, I get 13,300 hits for this search. I wonder how soon that search string will return more than one million hits?

The PDF Handbook for Microsoft Office


You probably have at least one PDF file saved on your hard drive. Perhaps dozens of them. They're the de facto standard for sharing files online. There are many reasons that PDF has become so popular. They have a consistent format. You can read them on virtually any computer. They offer many security options. And so on. When Eric Butow said he would like to write a handbook about saving Office documents as PDF files, I jumped at the idea. As I learned when I struggled to save my dashboard ebook as a PDF file, there's more to saving a PDF than merely, well, saving it. Eric has just completedThe PDF Handbook for Microsoft Office. His new book explains how to install the software, and specific steps you must take to save Excel, Word, PowerPoint, and Publisher documents as PDF files. It explains the settings that each program requires, and offers tips that you're bound to find useful. Depending on your requirements, you might not need to buy Adobe Acrobat to save your Office files as PDF. Eric's book describes more than a dozen low-cost, or free, programs that you can choose from. And he recommends web sites that offer many other choices.

Make Your Local Excel Guru Scratch His Head


If you took a poll of your favorite Excel gurus, their favorite guru likely would be Bob Umlas. Last year Bob wrote a little book titled, "This isn't Excel, it's Magic." In addition to useful tricks, this book describes some clever ones that are merely fun. Here's one you might pull on your local Excel guru:

In an empty spreadsheet, enter =NA() in cell D1. Copy the range D1:D2 to D3:D100, creating a column of alternating #N/A and blank cells.
Select all of column D. Press the F5 key to launch the Go to dialog. Choose Special.
In the Go To Special dialog, click on Formulas then choose OK. After you do so, Excel will select every cell that contains =NA().
Change the font to 100.
Press the Delete key to erase the =NA() formulas.
Click and drag to set the column back to its original width.
Choose Format, Row, Height to set the row height to 12.75.
Now look at your row number labels. You will see labels for only the even rows. The labels for your odd-numbered rows have disappeared. Now that is really odd.

Quick Way to Close Without Saving


Finally, here's a useful trick from Bob's book...
Suppose you're shutting down for the day and you want to close all your open files without saving any of them.
Hold down your Shift key then click on the X in the top-right corner of your screen to close Excel. Excel will ask if you want to save the first file. After you choose No -- with your Shift key still pressed -- Excel will close all your other files without asking, then shut down.
Or, suppose you want to close all your files without closing Excel.
Hold down your shift key and choose File. Because the Shift key is pressed you will see the Close All item in the File menu. If you continue to hold down the Shift key when you choose Close All, Excel will close all open files without asking whether you want them to be saved. If you release the Shift key before you choose Close All, Excel will ask whether to save each unsaved file.

Enough for now. More later,
Charley


Posted by: Jethro | 2:40 PM |

Range Names in Excel

From Unofficial Office Stuff

How to use names in Excel

Named ranges are one of the more powerful tools in Excel.

Jan Karel Pieterse of JKP Application Development Services has written a tutorial that will help you understand this technique.

Here are some of the chapter titles:
How To Define Range Names
How To Use Range Names
Absolute And Relative Addressing
The Context Of Names
Special Names
A Step Further: A Formula In A Defined Name
Dynamic Names
Passing Arguments To A Defined Name Formula
Bugs in Excel's Name Object


Posted by: Jethro | 2:34 PM |

Tuesday, April 11, 2006

IF statements and conditional formats in Excel

I found this little trick yesterday while running a training session in Excel.
Normally if you want to build an IF statement in Excel that is conditional on 2 or more premises than you need to use the AND function to evaluate them.
Eg. =IF(AND(A4=A5,A4=64),TRUE,"you are an ID10T")
However if you want to evaluate the same information in a conditional format than you do not need to build the function the same way.
In fact you can simply go =IF(A4=A5,A4=64) and it will work. Alternatively you could use =AND(A4=A5,A4=64) to achieve the same result.
So not only do you not need to complete the IF statement with the TRUE and FALSE arguments (in effect that is what the conditional format is anyway) you also don't have to use the AND function and can still test multiple conditions.
This is quite a different syntax to the formula function syntax.
If you want to test for either condition than you just need to put =OR(A4=A5,A4=64).


Posted by: Jethro | 3:47 PM |