Tuesday, September 25, 2007

New Website

Its finally here. SpyJournal v3.0 was launched this week.
These pages will remain for archival purposes. However please change your link for this site directly to www.spyjournal.biz. If you are reading via RSS please change your RSS Feed to http://www.spyjournal.biz/rss.xml for the whole site feed, or for a custom RSS Feed design your own.

Excel readers can subscribe to the Excel specific tips feed or email me to be added to my email subscription list.

Labels:


Posted by: Jethro | 12:52 PM |

Thursday, June 14, 2007

New Open XML file formats in Office 2007

If you have installed Office 2007 then you should be aware of the new file formats - xml.
If you haven't installed yet, then thre is plenty to learn about what they are and why they are great!

Joanna Bischel writes a great article about why the new xml formats are great and goes through some features in very easy to understand language.

First of all, Word, Excel and PowerPoint 2007 files now act as containers since they are actually compressed zip containers (just try changing the extension to a .zip and you'll know what I mean). To an end user, the file still looks like a single item but to the developer, the file is a package of parts, segmented in a logical tree structure, tied together by relationships which you can navigate through. No longer do you have the black box of a binary file from previous file formats. So knowing this, what are some of the benefits?

Read the rest of the article on her MSDN blog site.

Labels: ,


Posted by: Jethro | 3:10 PM |

Monday, June 11, 2007

Maximum Length of Macros

Alan Wyatt has some great Excel tips.

I found this one the other day.
There actually is a maximum length for a VBA macro. Fortunately there are some solutions if you get this situation occurring. Read Alan's write up about it.

Labels: ,


Posted by: Jethro | 10:26 AM |

Tuesday, May 29, 2007

Undiscovered Excel Funtions and Features

Microsoft has a web page on their site describing 30 little known features and functions in Excel.
Here's their blurb about it.

This article describes some of the most powerful and useful features and functions in Microsoft Excel that remain undiscovered by users. For example, you may create a new macro to perform a calculation when an existing formula or function can perform the task. Or, you may create a new macro to perform a task when you can use an existing feature that performs the task.
Some of the functions include:

Labels: , , ,


Posted by: Jethro | 10:54 AM |

Friday, May 25, 2007

Excel VBA Macro Shortcut Keys

VBA Macro shortcut keys can be created when recording a Macro. It pays to make sure you use a combination NOT used by some other shortcut. Eg don't use CTRL + S.
I had a client who recorded two macros and used CTRL + S and CTRL + Z for them. I found them when I tried to save the file and undo an edit.

Fortunately you can change them.
Go to Tools | Macro | Macros and select the Macro you want to change the shortcut key for. Click Options and a Dialog Box will appear allowing you to reset (or set) a shortcut key.

Remember that this only works for the macro in the specific workbook that this Macro is recorded in.

Labels: , ,


Posted by: Jethro | 11:02 AM |

Wednesday, May 23, 2007

Excel 2007 Tips

I am using Excel 2007 quite a bit now, and starting to really experience the pain of switching between 2003 and 2007.
At least I am starting to get the hang of the ribbon.

Charley Kyd writes about Excel2007 on ExcelUser.com

What We'll Do About Excel 2007
I'm not one of Excel 2007's biggest fans. Although the program offers many outstanding new features, it has a completely new user interface.
Microsoft designed the interface to make it easier for new and occasional users to discover Excel's features. They didn't design the interface to make frequent users more efficient.
In business, Excel users are much like factory workers. Our job is to quickly produce reports, analyses, and other forms of business insight. Unfortunately, Excel 2007 requires more mouse clicks and more mouse travel than does earlier versions. It's inherently less efficient. Therefore, even after you've learned the new interface well, you'll probably take longer to perform standard tasks in the new Excel.
Even so, there's no going back. Eventually, you'll need to adapt to Excel 2007, or to one of its successors. At ExcelUser, we also need to adapt.
Therefore, all future articles that describe hands-on use of Excel will be offered in two versions. One version will be for old Excel, the other for new Excel. Each such article will begin with a link to the other version. And slowly, we'll offer new-Excel versions of past articles written for old Excel.
Because Excel 2007 introduces new terminology, many of the new-Excel articles will reference The Excel 2007 User Interface. This article provides one location for defining those new terms.
I will be taking similar approach here.

Labels: ,


Posted by: Jethro | 9:16 PM |

Thursday, May 03, 2007

List of Macro Shortcuts in All Open Workbooks

Allen Wyatts Excel Tips has a beauty on extracting fromyour VBA the list of Macro Shortcut Keys in your VBA project.

Summary: Need a list of macro shortcut keys? It's not as easy in Excel as in some other Office applications, such as Word. It can be done, however, with a little ingenuity, as described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

If you develop a lot of macros, you may want to list all those macros along with the shortcut keys used to initiate them. Of course, coming up with the code to list the shortcut keys is the tricky part of this problem, as such an ability is not built into Excel directly. (You can do it in Word, but not in Excel. Go figure.)

Read the whole article and code on his site.

Labels: , ,


Posted by: Jethro | 9:52 AM |