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 |

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 |

Friday, March 23, 2007

Clear Excess Formatting in Excel Files

Microsoft have a very interesting tool on their website.

The Excel Excess Formatting Cleaner Add-in program removes all formatting that has been applied beyond the cell range that contains data, or beyond the cell range that is covered by shapes, in each worksheet in the workbook. This reduces the file size of a converted Lotus 1-2-3 file or of a file that has formatting applied beyond the data range.

You can also use this add-in program to resolve various printing and viewing problems that are caused by extra formatting that is applied outside the last cell (as defined by the UsedRange property to determine which cells in the worksheet contain data).
Once installed you can find the application in the File Menu under Close.
This worked brilliantly for the files I tried it on with significant file size decrements of 25%-60%.
As far as I can see the only forseeable problem with this is that sheets that are preformatted to take pasted values (eg. via a macro) could lose their formatting.

Labels: , , ,


Posted by: Jethro | 2:59 PM |

Thursday, January 11, 2007

Excel charts,

Doug Klippert has this great tip, a formula (that could be put into a User Defined Funciton (UDF), to convert the column number to its actual alpha representation.
The following formula extracts the Column letter:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

Read the explanation and the rest of the story on his post.

Crystal Xcelsius is a very funky charting add in that allows you to:

Create interactive Excel dashboards, business presentations and visual calculators from ordinary spreadsheets – then integrate them into PowerPoint, Word, PDF and the Web.
I am trialling this for a client and its is very cool how you can make on the fly "what if" changes to spreadsheets and powerpoint presentations.

Excel-VBA.com has a wealth of excellent topics and advice for excel and vba users. Some examples include, SUMPRODUCT, SUBTOTAL, the excel calculator, spreadsheet design, shortcuts and more. Well laid out also.

Tommy Flynn's site has a number of Excel VBA examples that you can copy and use including, hiding and unhiding sheets, input boxes, arrays and for next loops. Worth bookmarking.

Talking of which check out my Excel tags, Excel 2007 tags and VBA tags on del.icio.us

Labels: , ,


Posted by: Jethro | 9:11 PM |