How I did something (useful) in VB in half an hour
Monday, May 28, 2007 by Shreyas
It all started when I wanted to calculate some statistical measures like variance/std deviation automatically in Excel. I was planning to use the Analysis Toolpak in Excel. It's available in Tools -> Add-ins -> Select Analysis Toolpak -> Click Ok. Unfortunately, I found that neither was it installed on my system nor was it a free legal download (The addin was a file named proplus.msi). So I thought I might write some custom functions for these myself.
After some Googling, I had some idea to begin with.
To start with, go to Tools -> Macro -> Visual Basic Editor (Shortcut Alt+F11).
In VB editor go to Insert -> New Module (Alt+I+M)
Now I wanted to write a function. More Googling resulted in the basic syntax for writing methods. I wanted one thing in particular, for calculation of variance, a column of data had to be given as the input. What I was looking for was something similar to an ArrayList in java. Some more search and I found that I was supposed to use the data type Variant for this.
One observation was that Variant is somewhat similar to Object in java i.e. anything is passable as a Variant. And another was that, variants can be used directly without casting.
Take a look at the code I came up with:
Function variance(a As Variant) As Double
Dim answer As Double
Dim avg As Double
Dim sum As Double
Dim obj As Variant
Dim count As Integer
count = 0
sum = 0
avg = WorksheetFunction.Average(a)
For Each obj In a
sum = sum + ((obj - avg) ^ 2)
count = count + 1
Next
answer = sum / count
variance = answer
End Function
After saving this, I was able to use the method variance in the workbook as just another function like CONCATENATE or AVERAGE.
More lessons from this exercise are :
1) Standard Excel functions can be accessed using WorksheetFunction.methodName
2) How to traverse a list using for-next loops
The best way is to use functions already available in Excel. I did this because I wanted to learn how to write VB macros/custom functions in Excel and to write a simple VB function without any error.
After some Googling, I had some idea to begin with.
To start with, go to Tools -> Macro -> Visual Basic Editor (Shortcut Alt+F11).
In VB editor go to Insert -> New Module (Alt+I+M)
Now I wanted to write a function. More Googling resulted in the basic syntax for writing methods. I wanted one thing in particular, for calculation of variance, a column of data had to be given as the input. What I was looking for was something similar to an ArrayList in java. Some more search and I found that I was supposed to use the data type Variant for this.
One observation was that Variant is somewhat similar to Object in java i.e. anything is passable as a Variant. And another was that, variants can be used directly without casting.
Take a look at the code I came up with:
Function variance(a As Variant) As Double
Dim answer As Double
Dim avg As Double
Dim sum As Double
Dim obj As Variant
Dim count As Integer
count = 0
sum = 0
avg = WorksheetFunction.Average(a)
For Each obj In a
sum = sum + ((obj - avg) ^ 2)
count = count + 1
Next
answer = sum / count
variance = answer
End Function
After saving this, I was able to use the method variance in the workbook as just another function like CONCATENATE or AVERAGE.
More lessons from this exercise are :
1) Standard Excel functions can be accessed using WorksheetFunction.methodName
2) How to traverse a list using for-next loops
The best way is to use functions already available in Excel. I did this because I wanted to learn how to write VB macros/custom functions in Excel and to write a simple VB function without any error.
Posted in:
PonderWrite
|
|
|
