GAtkins

Greetings:

I am in the process of moving a program from Excel VBA to VBE 2005.

In each, I run numerous iterations of calculations over multiple years. Listed below is the heart of the computations, but there are many others that are standard addition, division, multiplication, etc.

For i = 1 To Iterations

For j = 1 To Years

Counter = Counter + 1

MyArray(i, j) = 1 + (InvRet + (((Math.Sqrt(-2 * objExcel.Application.WorksheetFunction.Ln(Rnd()))) * (Math.Cos(6.28318 * Rnd()))) * StdDev))

Next j

Next i

If you're interested, this is a Box-Muller transformation of a uniform distribution into a normal distribution.

In Excel VBA this program takes about 7 seconds to run 30 years with 2,000 iterations per year, plus all the other calculations in Excel VBA.

In VBE 2005, this same program with 30 years and 2,000 iterations in each year takes almost 2 minutes and 20 seconds to run.

Is there any way to speed up this VBE 2005 code/formula above

Thanks in advance for any suggestions.

Glenn

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

jo0ls

Major tweak:

Minor tweak:

Create an instance of the random class and use it instead of the rnd function. Create it at the form level instead of in a sub:

Private Rand as New Random

then you can use:

Rand.NextDouble

rnd()

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

GAtkins

jo0ls,

Thanks for the quick reply. That is mind-boggling fast.

I am not very well versed in math and trinonometry.

Should I use Math.Log or Math.Log10 to replace my Excel function of objExcel.Application.WorksheetFunction.Ln

I honestly do not know which to use.

Thanks again.

Glenn

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

jo0ls

Math.Log

(the naming is confusing here, but the documentation says that log is "log base e", so it is the same thing as ln)

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

GAtkins

That's what I thought. Thanks again.

Any ideas what I could use in place of this one:

MyArray(i, j) = 1 + (objExcel.Application.WorksheetFunction.LogInv(Rnd(), LNMean, LNSD) - 1)

This one creates a Log Normal distribution instead of the plain Normal Distribution from the 1st equation.

Thanks

Glenn

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

jo0ls

Unfortunately that's not a function that the framework provides. So you need to either write a version of the function, use a third party library or try and improve the office interop.

If you create a workbookfunction object, then you can get it faster, but it's still slow.

I get:

30153 ms - instantiating a WorkSheetFunction object and calling its LogInv

I've installed the office 2003 PIA, and added a reference to the Excel 11.0 object library and so I can use early binding.

Public Class Form1

Private app As Microsoft.Office.Interop.Excel.Application
Private wbf As Microsoft.Office.Interop.Excel.WorksheetFunction
Private rand As New Random

Sub New()
InitializeComponent()
app = New Microsoft.Office.Interop.Excel.Application
wbf = app.WorksheetFunction
End Sub

Private Sub Form1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Click
Dim sw As New Stopwatch
sw.Start()
Dim MyArray(1999, 29) As Double
For i As Integer = 0 To MyArray.GetUpperBound(0)
For j As Integer = 0 To MyArray.GetUpperBound(1)
MyArray(i, j) = 1 + (wbf.LogInv(rand.NextDouble, 500, 25) - 1)
' MyArray(i, j) = 1 + (objExcel.Application.WorksheetFunction.LogInv(rand.NextDouble, 500, 25) - 1)
Next
Next
sw.Stop()
Me.Text = sw.ElapsedMilliseconds
Debug.WriteLine(sw.ElapsedMilliseconds)
End Sub

End Class

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

jo0ls

If you need to use late binding then use:
...
Private app As Object
Private wbf As Object
Private rand As New Random

Sub New()
InitializeComponent()
app = CreateObject("Excel.Application")
wbf = app.WorksheetFunction
End Sub
...

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

JohnWein

You have to avoid the call to Excel within a loop.  Any call to Excel takes nearly a millisecond.  An actual math computation takes nanoseconds.  If you must use an Excel function, populate a lookup table with values from the Excel function.

Edit:  Here's an example:

Public Class Form1< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Private NormSInv(100) As Double

Private MyArray(60000) As Double

Private Sub Form1_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) _

Handles Me.Click

Dim I As Integer

Dim sw As New Stopwatch

sw.start()

For I = 0 To 60000

MyArray(I) = ComputeLogNormal(Rnd(), 3.5, 1.2)

Next

sw.Stop()

Debug.Print(sw.ElapsedMilliseconds)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) _

Dim I As Byte

Dim app As New Excel.Application

Dim Wbf As Excel.WorksheetFunction = app.WorksheetFunction

For I = 1 To 99

NormSInv(I) = Wbf.NormSInv(0.01 * I)

Next

NormSInv(0) = Wbf.NormSInv(0.0001)

NormSInv(100) = Wbf.NormSInv(0.9999)

End Sub

Friend Function ComputeLogNormal(ByVal Probability As Double, _

ByVal Mean As Double, _

ByVal StdDev As Double) As Double

Dim NSI As Double

Dim PFixed As Byte

Probability = 100 * Probability

PFixed = Fix(Probability)

NSI = NormSInv(PFixed)

NSI = NSI + (Probability - PFixed) * (NormSInv(PFixed + 1) - NSI)

Return Math.Exp(Mean + StdDev * NSI)

End Function

End Class

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

jo0ls

Excellent. And GAtkins can ditch NormSInv by using this implementation of this algorithm instead.

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

GAtkins

Wow, excellent guys!

I started this thread and it quickly went off into the abyss way over my head.

Great information though and thanks again.

Glenn

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

GAtkins

The code from above, which is slightly modified below, seems to produce the same number every time. On my computer it produces 1.1051 every time.

It appears to not be generating a random number every time through the loop.

Does anybody have any ideas as to why

Thanks

Glenn

Imports Microsoft.Office.Core

Imports Microsoft.Office.Interop.Excel

Public Class Form1

Private NormSInv(100) As Double

Private MyArray(60000) As Double

Private Sub Form1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Click

Dim I As Integer

Dim sw As New Stopwatch

sw.Start()

For I = 0 To 2000

MyArray(I) = ComputeLogNormal(Rnd(), 0.1, 0.158)

Debug.Print("My Array " & I & " = " & MyArray(I))

Next

sw.Stop()

Debug.Print(sw.ElapsedMilliseconds)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim I As Byte

'Dim app As New Excel.Application

'Dim Wbf As Excel.WorksheetFunction = app.WorksheetFunction

For I = 1 To 99

'NormSInv(I) = Wbf.NormSInv(0.01 * I)

Next

'NormSInv(0) = Wbf.NormSInv(0.0001)

'NormSInv(100) = Wbf.NormSInv(0.9999)

End Sub

Friend Function ComputeLogNormal(ByVal Probability As Double, ByVal Mean As Double, ByVal StdDev As Double) As Double

Dim NSI As Double

Dim PFixed As Byte

Probability = 100 * Probability

PFixed = Fix(Probability)

NSI = NormSInv(PFixed)

NSI = NSI + (Probability - PFixed) * (NormSInv(PFixed + 1) - NSI)

Return Math.Exp(Mean + StdDev * NSI)

End Function

End Class

### Re: Visual Basic Express Edition Any Way To Speed Up This Math Formula Loop?

JohnWein

EXP(.1) = 1.1051. It does what you have programmed.