Archive for August, 2012

Improving The Speed Of Excel Macros

Excel macros

In the past, I’ve given an introduction to building excel macros, either by recording or by directly writing code into the program. Macros are an amazing way to automate and optimize tasks while also diminishing errors. That being said, especially when starting, macros sometimes take a bit of time to run. Why? They do not have an optimal code. Here are 3 important things to do in order to avoid having a macro run for ages:

-Only Use Loops For Specific Circumstances

Loops can be the best thing ever but if you have to do a loop through a few hundred lines, you might grow grey hair in the process. It’s important to use loops only if you know that the amount of data can support it. If you are unsure, I would suggest using other options when possible such as an advanced filter, formulas, etc.

-Use A Log

One great option when you have macros with many different subsections is to write down in a separate sheet, the time at specific parts of the macro. This will help you determine which steps are taking longer to run, in order to further optimize those parts.

-Reduce Calculations/Processing Time

Several things can be done in order to improve the speed of your macro. One thing is to allow Excel to run it without you seeing every step. You can also diminish the number of warnings that excel will give you, set calculations to manual (instead of automatic). Here are some lines that can be added at the start of your macro:

With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlManual
End With

Then you would simply set everything back at the end of the macro:

With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlAutomatic
End With

All of those things can end up making your macro 2 times, 5 times or even 100 times faster depending on what you’re working on so they are without a doubt worth looking into.

Financial Advice For College Graduates Looking to Experiment

Personal finance

You’re done with college. You got your credentials. You found a job. You’re making decent money for the first time ever. What’s next?

You want to experiment and see what life has to offer. You want to break out of your bubble. You want a different life from the one that you had in college. You want to figure out what this money-saving stuff you hear about is all about.

I needed a cash advance and had to resort to my emergency fund to make the repairs. I wanted to share financial advice for college graduates looking to get ahead:

Build your emergency fund.

My friend, things happen in life. We always assume the best-case scenario. The reality is that anything can happen. I didn’t realize the importance of an emergency fund until I crashed my car in the snow one cold evening. I had to resort to my emergency fund to make the repairs.

An emergency fund or savings account or whatever you want to call it, is money that you have waiting for you in case you need. You don’t have to invest this money or take any chances with it. Just let it sit until you absolutely need. Believe me, you’ll need it one day.

Retirement.

I don’t want to bore you with retirement talk. I just want you to browse around to see if your employer offers any sort of retirement plan where they match contributions. You don’t want to miss out on free money.

Kill student debt.

I’ve written pretty extensively about crushing student debt. You already know what needs to be done and I’m not here to preach. I just want you to decide what you plan on doing about your student debt before it’s too late and the creditors start chasing you for the money,

Credit cards.

Ah the always polarizing topic of credit cards. If you want to get ahead financially you want to use credit cards and not let credit cards use you. Of course this is easier said than done.

How can you use your credit card like a champ?

  • Only spend money that you have.
  • Keep your limit low if you don’t have control.
  • Get relevant credit card rewards.
  • Pay off your balance every single month.
  • Repeat.

Don’t let your balance become outstanding! That’s not a good thing.

Get roommates.

I won’t try to convince you to live at home. As much money as you can save, it’s just not worth it. There’s no price tag for your freedom. I recommend finding solid roommates that you can rent a place with to save money on rent and to motivate each other to work harder. It’s important that you watch who you surround yourself with in your years after college.

Wait to buy a home.

The most important financial decision that you’ll have to make in your late-20s is if buying a home is right for you. Most college graduates feel that getting a home right after school is the logical step and pretty much mandatory. I want you to challenge that. I want to see you actually run some real numbers to determine if it’s even worth it for you to put so much money into a home.

That’s what you can do in your years after college to get ahead of your friends. You don’t have to do all of this. You can slowly apply these tips to your life.

Using Excel In The Clouds

Google Cloud Spreadsheets

A few months ago I discussed using Google docs and provided a spreadsheet that updates stock prices live. One major selling point of course of using cloud based software is that it is:

-backed up automatically
-accessible from any location
-can be used by multiple users without always sending updated versions by email

Excel Still Heads And Shoulders Above Competition

That being said, nothing comes close to how broad excel has became over the years. As the writer of an excel blog, you can probably imagine that since 95% of what I write about is not yet possible on alternatives like Google docs, I remain a very heavy excel user.

Excel In The Clouds

I do know that Microsoft is working on some cloud based version of excel and that might end up being what we’ll eventually all be using. In the meantime however there are alternative methods. Using software such as Dropbox or Google Drive, you can have those services automatically sync specific folders on your computer. How does it work?

1-You install their software and select a directory
2-Each time you update a file in that directory and save it, the updated version will be updated on the web version but also on linked devices.

By doing that you get several of the benefits of cloud computing such as;

-automatic backups
-possible to open your file from any location that is connected
-all of the powerful excel features
-you can also sync other files such as photos, documents, etc

There are some drawbacks though compared to using a solution like Google docs:

-can’t have simultaneous users working on the same file at once
-the file must be saved in order for the web to have the most recent file

I personally have been using Dropbox and am incredibly happy with it. Google Drive works fine too but I’m sticking with Dropbox for now. There are also alternatives offered by Microsoft and others.

Have you been using such services and if so how has been your experience?