Annual Equivalent Rate

To evaluate deeply the revenue of a specific asset, we should take into account the number of times a year that the interest is paid.

So, if you want to know which is the "real" interest rate of a specific asset you can use the following VBA script on Excel.

Sub equivalent_rate()

Dim rate As Double
Dim eq_rate As Double
Dim n As Integer

rate = InputBox("Insert the annual rate in percentage") / 100

n = InputBox("Insert the number of times you receive the interest")

eq_rate = (((1 + rate / n) ^ n) - 1) * 100

MsgBox ("Your equivalent interest rate is " & eq_rate)

End Sub

How to import data from internet into Excel?

Do you know how to Import data from a website into Excel?

Using VBA it is pretty simple, take a look on the script below that downloads the performance of the General Motors Shares during the last month.

If you need to download information from another website just change the URL!

How to stop an endless loop (Office 2010, 2013...)

Stopping endless loops in VBA

One of the most disappointing things for a VBA beginner is the handling of the endless loops.

When using loops ("for loop", "while loop", "do loop", etc) sometimes we use conditions that are always true and the macro runs forever,  in programming we call it an endless loop.

To give you an example, think about, but please do not run, the following macro:

Sub endless()

While Worksheets(1).Range("A1").value=""

Worksheets(1).Range("A1").value=Worksheets(1).Range("A1").value

Wend

End Sub

If on the first worksheet of the excel file, the cell(A1) is blank this macro will run forever, because the condition to be true is having the cell(A1) blank and this condition will always be true. The macro will run forever, we created an endless loop.

To stop the macro we would have to close the excel program (clicking on task manager-> choose the task -> end task) and to lose all the unsaved data in our file.

However, fortunately there is an easier way to stop an endless loop:

1. Before running the macro write the following script on the immediate window: Application.EnableCancelKey=xlInterrupt
2. Run the macro.
3. If your macro has an endless loop, or is taking much longer than you thought. Please press ESC and it will stop immediately. J

Please check the following video, if you did not get it: