Monday, January 30, 2006
Trendline coefficients and regression analysis
This is a great tutorial by Tushar. It tells you a lot about regression, trendlines and using them in VBA. Check it out!
Trendline coefficients: "Trendline Coefficients and Regression Analysis"
Trendline coefficients: "Trendline Coefficients and Regression Analysis"
Send File Path-Email via Groupwise
This is what I had to do lots of times. Send the file path of an Excel workbook to my supervisor. So I created this macro to send the activeworkbook full file path to the specified user. This can actually be well modified to create an AddIn, but for now, this is what is going to be. Insert a module in the personal.xls and insert this code. A reference to "Groupware Type library" is a must to make this macro work.
Option Explicit
Private ogwApp As GroupwareTypeLibrary.Application
Private ogwRootAcct As GroupwareTypeLibrary.Account
Sub SendEmail()
Dim strRecipient As String
On Error GoTo SendEmail_Error
strRecipient = InputBox("Enter the email address of recepient", "Recipient Name")
If strRecipient = "" Then
MsgBox "No Recipient specified", vbCritical
Exit Sub
End If
Const NGW$ = "NGW"
Dim ogwNewMessage As GroupwareTypeLibrary.Mail
Dim StrSubject As String, StrBody As String
Dim strWkbkName As String, strWkbkPath As String
strWkbkName = ActiveWorkbook.Name
StrSubject = strWkbkName & "-File"
strWkbkPath = ActiveWorkbook.Path
StrBody = "Please find the file-" & strWkbkName & " here- " & strWkbkPath & "\" & strWkbkName
StrBody = StrBody & vbCrLf & vbCrLf & "Thanks"
If ogwApp Is Nothing Then 'Need to set object reference
DoEvents
Set ogwApp = CreateObject("NovellGroupWareSession")
DoEvents
End If
Dim StrLoginName As String, StrMailPassword As String
Set ogwRootAcct = ogwApp.Login
Set ogwNewMessage = ogwRootAcct.WorkFolder.Messages.Add("GW.MESSAGE.MAIL", egwDraft)
DoEvents
With ogwNewMessage
.Recipients.Add (strRecipient)
'Assign the SUBJECT text
.Subject = StrSubject
'Assign the BODY text
.BodyText = StrBody
'Send the message
.Send
DoEvents
End With
On Error GoTo 0
Set ogwRootAcct = Nothing
Set ogwNewMessage = Nothing
Set ogwApp = Nothing
Exit Sub
SendEmail_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendEmail"
Set ogwRootAcct = Nothing
Set ogwNewMessage = Nothing
Set ogwApp = Nothing
End Sub
'http://www.phhp.ufl.edu/~smanamal/misc/Groupwise.htm
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=277
Private ogwApp As GroupwareTypeLibrary.Application
Private ogwRootAcct As GroupwareTypeLibrary.Account
Sub SendEmail()
Dim strRecipient As String
On Error GoTo SendEmail_Error
strRecipient = InputBox("Enter the email address of recepient", "Recipient Name")
If strRecipient = "" Then
MsgBox "No Recipient specified", vbCritical
Exit Sub
End If
Const NGW$ = "NGW"
Dim ogwNewMessage As GroupwareTypeLibrary.Mail
Dim StrSubject As String, StrBody As String
Dim strWkbkName As String, strWkbkPath As String
strWkbkName = ActiveWorkbook.Name
StrSubject = strWkbkName & "-File"
strWkbkPath = ActiveWorkbook.Path
StrBody = "Please find the file-" & strWkbkName & " here- " & strWkbkPath & "\" & strWkbkName
StrBody = StrBody & vbCrLf & vbCrLf & "Thanks"
If ogwApp Is Nothing Then 'Need to set object reference
DoEvents
Set ogwApp = CreateObject("NovellGroupWareSession")
DoEvents
End If
Dim StrLoginName As String, StrMailPassword As String
Set ogwRootAcct = ogwApp.Login
Set ogwNewMessage = ogwRootAcct.WorkFolder.Messages.Add("GW.MESSAGE.MAIL", egwDraft)
DoEvents
With ogwNewMessage
.Recipients.Add (strRecipient)
'Assign the SUBJECT text
.Subject = StrSubject
'Assign the BODY text
.BodyText = StrBody
'Send the message
.Send
DoEvents
End With
On Error GoTo 0
Set ogwRootAcct = Nothing
Set ogwNewMessage = Nothing
Set ogwApp = Nothing
Exit Sub
SendEmail_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendEmail"
Set ogwRootAcct = Nothing
Set ogwNewMessage = Nothing
Set ogwApp = Nothing
End Sub
'http://www.phhp.ufl.edu/~smanamal/misc/Groupwise.htm
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=277
Friday, January 27, 2006
Auto increment a cell
Suppose if you want to increment a cell by 1 to see how your formulae handle that change, may be a simulation. That means that cell would be incremented by 1 everytime you hit F9 (keyboard short cut to make Excel recalculate). To achieve this do the following:
Then enter this formula in the cell which you want to increment, suppose it's A1.
Formula for A1: =A1+1.
That's it! Now, every time you hit F9, that cell will be incremented by 1. If you want it increment by some other number, use that number in the formula.
1. On the Tools menu, click Options, and then click the Calculation tab.-MS Excel Help
2. Select the Iteration check box.
3. Set the maximum number of times Microsoft Excel will recalculate, type one as the number of iterations in the Maximum iterations box
Then enter this formula in the cell which you want to increment, suppose it's A1.
Formula for A1: =A1+1.
That's it! Now, every time you hit F9, that cell will be incremented by 1. If you want it increment by some other number, use that number in the formula.
Monday, January 23, 2006
List of resources that are available to help you learn Visual Basic for Applications programming
Check this article @ MS's website to know where to get help for VBA programming.
List of resources that are available to help you learn Visual Basic for Applications programming
List of resources that are available to help you learn Visual Basic for Applications programming
Saturday, January 21, 2006
Random Numbers
Recently, I had the need of generating different types of random numbers. Although, Excel's Data Analysis Toolpak can generate good random numbers, I had to create these functions to meet my needs.
First: Unique Random Numbers
This function was modified from ozgrid's RandLotto function, which returns string. This function will return an array of unqiue random integers in the given range. If you want to return more than one number then this function should be entered as an array formula with Ctrl + Shift + Enter.
Second: Gaussian or Normal Random Numbers with specified mean and Standard deviation(SD). Now, this function is different than the Analysis toolpak utility because it generates random numbers on a fixed mean and Standard deviation. This function will allow the user to generate random numbers each with different mean and SD.
Third: Generate white noise or Guassian or Normal random numbers with mean = 0 and SD =1. Although, this could be achieved by the previous function, this function uses Box-Muller method. In this function too, you can use different mean and SD, although if not provided it would take mean =0 and SD=1
This is a screenshot of the Excel file. Also, the original Excel file can be downloaded here.
First: Unique Random Numbers
This function was modified from ozgrid's RandLotto function, which returns string. This function will return an array of unqiue random integers in the given range. If you want to return more than one number then this function should be entered as an array formula with Ctrl + Shift + Enter.
Function UniqueRand(lngSmallest As Long, lngHighest As Long, HowManyNos As Long) As Long()
'modified from http://www.ozgrid.com/VBA/RandomNumbers.htm
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Application.Volatile
ReDim iArr(lngSmallest To lngHighest)
For i = lngSmallest To lngHighest
iArr(i) = i
Next i
For i = lngHighest To lngSmallest + 1 Step -1
r = Int(Rnd() * (i - lngSmallest + 1)) + lngSmallest
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
Dim resultArr() As Long: ReDim resultArr(HowManyNos - 1, 0)
For i = lngSmallest To lngSmallest + HowManyNos - 1
resultArr(j, 0) = iArr(i)
j = j + 1
Next i
UniqueRand = resultArr
End Function
'modified from http://www.ozgrid.com/VBA/RandomNumbers.htm
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Application.Volatile
ReDim iArr(lngSmallest To lngHighest)
For i = lngSmallest To lngHighest
iArr(i) = i
Next i
For i = lngHighest To lngSmallest + 1 Step -1
r = Int(Rnd() * (i - lngSmallest + 1)) + lngSmallest
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
Dim resultArr() As Long: ReDim resultArr(HowManyNos - 1, 0)
For i = lngSmallest To lngSmallest + HowManyNos - 1
resultArr(j, 0) = iArr(i)
j = j + 1
Next i
UniqueRand = resultArr
End Function
Second: Gaussian or Normal Random Numbers with specified mean and Standard deviation(SD). Now, this function is different than the Analysis toolpak utility because it generates random numbers on a fixed mean and Standard deviation. This function will allow the user to generate random numbers each with different mean and SD.
'This function will generate Gaussian or Normal Random Numbers based on given mean and SD
Public Function GenGaussNoise(mean As Double, SD As Double, HowMany As Long) As Double()
Dim x As Double, i As Long, j As Long
itr = 50
Dim dblResArr() As Double: ReDim dblResArr(HowMany - 1, 0)
For j = 1 To HowMany
x = 0
For i = 1 To itr
Randomize
U = Rnd
x = x + U
Next i
' /* for uniform randoms in [0,1], mu = 0.5 and var = 1/12 */
' /* adjust X so mu = 0 and var = 1 */
x = x - itr / 2 ' /* set mean to 0 */
x = x * Sqr(12 / itr) ' /* adjust variance to 1 */
dblResArr(j - 1, 0) = mean + SD * x
Next j
GenGaussNoise = dblResArr
End Function
Public Function GenGaussNoise(mean As Double, SD As Double, HowMany As Long) As Double()
Dim x As Double, i As Long, j As Long
itr = 50
Dim dblResArr() As Double: ReDim dblResArr(HowMany - 1, 0)
For j = 1 To HowMany
x = 0
For i = 1 To itr
Randomize
U = Rnd
x = x + U
Next i
' /* for uniform randoms in [0,1], mu = 0.5 and var = 1/12 */
' /* adjust X so mu = 0 and var = 1 */
x = x - itr / 2 ' /* set mean to 0 */
x = x * Sqr(12 / itr) ' /* adjust variance to 1 */
dblResArr(j - 1, 0) = mean + SD * x
Next j
GenGaussNoise = dblResArr
End Function
Third: Generate white noise or Guassian or Normal random numbers with mean = 0 and SD =1. Although, this could be achieved by the previous function, this function uses Box-Muller method. In this function too, you can use different mean and SD, although if not provided it would take mean =0 and SD=1
Public Function GenWhiteNoise(HowMany As Long, Optional mean As Double = 0, Optional variance As Double = 1) As Double()
'Returns a normally distributed random variate with mean 0 and variance 1, a.k.a. "White Noise".
' Uses the Box-Muller method
Dim Value1 As Single, Value2 As Single, Fac As Single, Rsq As Single, SD As Double
Dim dblResArr() As Double: ReDim dblResArr(HowMany - 1, 0)
SD = Sqr(variance)
For i = 1 To HowMany
Do
Randomize
Value1 = 2 * Rnd - 1
Value2 = 2 * Rnd - 1
Rsq = Value1 ^ 2 + Value2 ^ 2
Loop Until Rsq > 0 And Rsq < 1
Fac = (-2 * Log(Rsq) / Rsq) ^ 0.5
If Rnd < 0.5 Then
dblResArr(i - 1, 0) = mean + SD * Value1 * Fac
Else
dblResArr(i - 1, 0) = mean + SD * Value2 * Fac
End If
Next i
GenWhiteNoise = dblResArr
End Function
'Returns a normally distributed random variate with mean 0 and variance 1, a.k.a. "White Noise".
' Uses the Box-Muller method
Dim Value1 As Single, Value2 As Single, Fac As Single, Rsq As Single, SD As Double
Dim dblResArr() As Double: ReDim dblResArr(HowMany - 1, 0)
SD = Sqr(variance)
For i = 1 To HowMany
Do
Randomize
Value1 = 2 * Rnd - 1
Value2 = 2 * Rnd - 1
Rsq = Value1 ^ 2 + Value2 ^ 2
Loop Until Rsq > 0 And Rsq < 1
Fac = (-2 * Log(Rsq) / Rsq) ^ 0.5
If Rnd < 0.5 Then
dblResArr(i - 1, 0) = mean + SD * Value1 * Fac
Else
dblResArr(i - 1, 0) = mean + SD * Value2 * Fac
End If
Next i
GenWhiteNoise = dblResArr
End Function
This is a screenshot of the Excel file. Also, the original Excel file can be downloaded here.
