I did write an introduction to macros in the past which is still very much relevant. That being said, if you use Excel 2007, you might be using slightly different menus so I thought I could go over the menus in order to give a brief introduction to building macros in Excel 2007. First thing to know, you should be looking in the Developer tab that you can see here:

Then, here is what each button will help you do:

–Visual Basic: this will help you read, write and edit code as I described in a previous post
–Macros: If you want to look at the different macros by name to be able to either run them or start editing them, this is the way
–Record macros: In order to create macros by recording them. It’s the easiest way to do it and the perfect way to get started
–Design Mode: Once you select this mode, you will be able to modify buttons without having the macros start running when you touch the buttons
-Insert: If you need to add a button, a form, a dropdown menu or anything else of that nature, this is where you’d go.

Overall, while I’m not a fan of the navigation in Excel 2007 in general, I do still think that for building macros, it’s much better than previous versions. What are your thoughts?

The other day I was given a challenge by a reader who needed to download some images from the web. First off, why not do it automatically? The first reason would be to save time because with excel vba you can automate the task. It automate the task. In this case, the image is a stock chart which can change every day so being able to quickly update the excel file required being able to download it.

What Is Necessary To Do This?

The most important is to know the exact address or URL of the file including the name such as:

http://www.example.com/name.jpg

Once you know that, you should be ok. In my opinion there are 2 different tasks that could be ran when downloading a file, you can

#1-Download the file and save it to a directory

The first one requires a bit of code but it’s straight forward once you do have it in place, you can simply use the following:

Private Declare Function URLDownloadToFile Lib “urlmon” _
Alias “URLDownloadToFileA” (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Private Const ERROR_SUCCESS As Long = 0

Sub LancementProcedure()
DownloadFile “http://finviz.com/chart.ashx?t=INTC&ty=c&ta=0&p=w&s=l”, “C:\stock.png”
End Sub

Public Function DownloadFile(ByVal sURL As String, ByVal sLocalFile As String) As Boolean
Dim lngRetVal As Long
DownloadFile = URLDownloadToFile(0&, sURL, sLocalFile, 0&, 0&) = ERROR_SUCCESS
End Function

The 2 main parameters that you must change are the Downloaded File URL and the location+name where you’re saving it.

#2-Download the file and add it to the excel spreadsheet

Sheets(1).Shapes.AddPicture “http://finviz.com/chart.ashx?t=” & Ticker & “&ty=c&ta=1&p=d&s=l” _
, msoFalse, msoTrue, 380, 60, 800, 410

In this case, it will simply add the image to the spreadsheet of the size and location that is mentioned. I also run a couple of lines to always delete the previous chart before adding a new one.

You can see what it looks like here:

And download the spreadsheet here

Over the past few months, I’ve been interacting with many of you who have been visiting Experiglot, commenting on posts, sending me emails with questions and comments, etc. I’ve came to the conclusion that there were two big needs and today’s post is to announce that I am finally able to help out on both.

#1-Improve Your Excel Skills

Many of you have projects related to work or hobbies that require using excel either to improve, automate or even just to track and organize data. Searching this wensite and the web in general is great but we sometimes lack structure or don’t know what to look for. I’ve created an ebook that includes tens of functions along with a few spreadsheets wwith examples of vlookup, sumif, macros, etc. It’s a fairly large package that can enhance your excel skills and knowledge significantly, all for a very reasonable $9.95!!!

Find out more about the ebook here

#2-Get Instant Help For Your Spreadsheet

I know the feeling. You’ve been trying to get this thing done in excel for hours and maybe even days and it’s just not working. At this point, you need to get it done. I would be more than happy to help. You can send me your spreadsheet along with a description of what you’re trying to do. For a modest fee, I’ll take a look and get back to you within 24 hours. In over 90% of cases, you’ll have it fixed while more complex spreadsheets might require additional work or be beyond the scope of what I can reasonable expect to do.

This is guaranteed. If the spreadsheet does not do what you were looking for, I’ll be more than happy to pay you back.

Find out more about getting my help for your spreadsheets here

The other day, I received a comment from a reader:

“how can i import EV/EBITDA into excel spreadsheet and to make it in a way that it will updated automatically. let’s say from a website like www.advfn.com”

Basically, he wanted to have a list of stocks and gather data from that specific website. That seemed like a challenge I was up for:) It should actually be fairly easy to do. First off, I created a sheet with a list of symbols. Then I created a tab where the web query would be done.

Then, I only needed to create a loop that would:

-go through each name
-do the web query for that ticker
-do a vlookup to find that information

So let’s do this step by step:

#1–go through each name

#2-do the web query for that ticker

#3-do a vlookup to find that information

#4-publish that info

Here is the final result! You can download the file here

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.