OccupyOccupyWallStreet

http://www.occupyoccupywallstreet.org/

‘

Advertisements

Two Quicks Tips for Faster Macros

For someone who already knows these two lines of code, they are simple and obvious. Yet they are also vital to working with any large data set. The first time I discovered them, my macro’s runtime dropped from six minutes to three seconds. I now include them in almost any long-running macro I write, with a few exceptions we’ll get to in a minute.

This first line of code controls display changes while a procedure is running. At the beginning and end of your macro, insert these lines of code:

Application.ScreenUpdating = False

… your code …

Application.ScreenUpdating = True

This code is especially important for any macro that is generating a large amount of data, like a report generator.

Note that while any change in cell values will not update until the macro is finished, changes to the StatusBar will be displayed. This can be useful if your macro’s runtime is several minutes. Updating the StatusBar with the progress of the macro lets the user know that the program has not frozen.

The second line of code prevents Excel from constantly re-calculating the formulas in your workbook while your macro is running:

Application.Calculation = xlCalculationManual

… your code …

Application.Calculation = xlCalculationAutomatic

This is especially useful in workbooks pulling lots of Bloomberg data, but it will increase the speed of many complicated macros.

You have to be careful when you use this because sometimes it can back fire. For instance, let’s say Cell B2 is your pre-tax income, which will be updated in your macro, and B3 is your post-tax income, calculated as a formula. If you change B2, B3 will not change until after the macro has finished running. You can remedy this by temporarily turning Calculation back on.

While not perfect for every situation, these two pieces of code can cut the runtime of your macro enormously, and turn a seemingly impossible project into a snap.

The Importance of an Excel Homepage

The first file I open when I get to work is ‘HomeBase.xls’. This is my Excel Homepage.

HomeBase has a variety of features packed into a single screen that help me throughout my day:

• A list of upcoming important calendar items – synced with Outlook each morning – that I can open by double-clicking.
• A to-do list that highlights items when they’re past due and can be crossed out with a right-click.
• A list of frequently used Excel files that can be quickly opened from HomeBase instead of browsing for them.
• Shortcuts to all my most frequently used macros
• A mini mail client – automatically synced every minute with Outlook – that only shows the unread items in important mail folders
• Quick access to frequently used programs like Bloomberg, Google Chrome, and the Windows Calculator
• The temperature and chance of precipitation for today and tomorrow
• My stock portfolio with live quotes from Bloomberg
• And most importantly… what the cafeteria is serving for lunch today, automatically pulled from an e-mail sent by the front desk

That is not even the complete list, and yet the entire homepage fits on one screen without any scrolling necessary. I’ve included a stripped-down version of my HomeBase in the example files on the right. It is optimized for 1280×1024, although I use 1600×1200. It has the mailbox feature – right-click to mark as read, double-click to open – the to-do list feature, and the calendar feature.

Creating a homepage is a simple way to improve your productivity and create a more personalized work experience.

For Excel pros, it can be a fun way to expand your skills as you try and create more advanced widgets to add-on.

For beginners, Excel Solutions offers extremely affordable basic homepages with full-service support. Homepages can be expanded piece-by-piece as new needs are discovered, or we can build an advanced Homepage from scratch.

The possibilities for what you can include in your homepage are endless. Just imagine how you can make your day easier or more organized and we can turn it into reality.

Ask the Master: Unique List from Repeated Data

BostonAnalyst asks:

I have a list of product sales data that I need to summarize once a week, but the products in the list change from week to week. There are hundreds of products and it takes me an hour to type up a list with each product listed just once so I can run ‘SUMIF’ and average formulas.

Is there an easier way?

There is always an easier way, BostonAnalyst. Almost any tedious task can be solved with macros or VBA programming. But it’s better to avoid programming if you can. The simplest solution is always the best. And fortunately, there is a non-progamming solution to this problem.

1. Open a new Worksheet
2. Paste your list of products in ‘A1’ and sort the data.
3. In cell ‘B1’ use this formula: =COUNTIF($A$1:A1,A1)
4. Copy the formula all the way down to the bottom of your data
5. Copy column B and paste as values in the same column
6. Sort columns A:B by column B.

You now have your unique list of data. This is a simple solution that requires no programming and can be done in a few minutes no matter how much data you have.

If you’re interested in a more comprehensive solution, we can discuss building a custom-made reporting tool. Your weekly summary report can then be generated with the click of a button and have a more professional look to it.