Skip to main content

2. Excel VBA: Creating our first macro

  1. Open the Visual Basic editor. Go to the developers tab. Click on Visual Basic. A new window will open.
    Opening visual basic editor
  2. Go to the top menu and select Insert –> Module.
    Visual basic editor windows
  3. On the left of your screen you can see that a new window is created.
    A new module is created
  4. Enlarge the window so we can start making our first macro. We will name our macro “HellowProgram”. msgBox will create a message box with the text “Have a nice day”. “End sub” indicates  the end of our program. Close the window.

    Our first macro

  5. Now we will link the macro to a button. So it will be activated when we click on it. Your cursor will turn into a cross. By holding the alt key you can make sure that excel uses the existing grid lines. You can select multiple cells if you want.
    inser button to excel workbook
  6. Select the name of the macro we’ve just made. I named my macro ” HelloProgram” . Click on “Ok”.

    assign macro in excel

  7. Now you can use the button in your workbook. But first we want to edit the text. Edit the text of the button by right clicking on it. And clicking “Edit Text”.
    edit text of button
  8. Edit the text. You can name it any way you like.
  9. Click on the button. the “Have a nice day” message box will appear. Our first macro works!
    pop up message 2
  10.  Click on “Ok” to close the window.

 

 

How To Use Calculate Compound Interest With Excel

So maybe you have an investment, and you want to know what it will be worth in a few years? I will show you a method how you can easily find out.

“Compound interest is the eighth wonder of the world. He who understands it, earns it … he who doesn’t … pays it.” – A. Einstein

compound interest in excel

The formula (by year)

Result = initial investment * ( 1 + interest rate) ^ number of periods

The formula (by Month)

Result = initial investment * ( 1 + interest rate/ periods per year) ^ (number of periods)

Weekly graph of website visitors in microsoft excel

Excel: How to make a chart that contains big differences in data.

I got an e-mail this weekend asking following question:
How do I represent many very large and very small data values together in a MS Excel graph?

There are multiple ways to do this depending on which data you want to show your users.

Split the axis in two parts

Let’s start of making a table with some data that we want to visualize.

Most of the values in the graph are almost invisible. This makes it hard to read our data. How can we fix this?

Start by decreasing the chart’s plot area. So we can make room for a second graph.

Click on the area around the graph and press CTRL+D . This will make a duplicate on top of the current excel chart.

Resize and align both charts. The second chart comes above the first plot area. Don’t forget to delete the column names from second chart.

Select y-axis. Go to “axis options” Under the section “bound” you lower the maximum range to that the small values on the graph become visible. Then take the second y-axis and increase the minimum. Delete the x-axis from the second graph.

Now go to Insert -> shapes and pick a rectangular shape.

Now use this triangle to cover up the line between the graph. Then go to settings and give the rectangle a white filling.

 

Plot with a logarithmic scale

This is a more scientific approach to the same problem. But this makes it less easy to comprehend for the general public.

First select the y-axis -> right mouse button -> format axis.

This will give the following result.

Do you have a different approach to this problem. Let me know in the comments.