Archive

Archive for January, 2006

Trendline coefficients and regression analysis

January 30th, 2006 No comments

Tushar has a great tutorial on his website. It tells you a lot about regression, trendline and using them in VBA. Check it out!

Trendline coefficients: “Trendline Coefficients and Regression Analysis”

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized, Useful Procedures Tags: ,

Send File Path-Email via Groupwise

January 30th, 2006 No comments

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

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized Tags:

Auto increment a cell

January 27th, 2006 No comments

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:

1. On the Tools menu, click Options, and then click the Calculation tab.
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

-MS Excel Help

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.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized Tags:

List of resources that are available to help you learn Visual Basic for Applications programming

January 23rd, 2006 No comments

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

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized Tags:

Random Numbers

January 21st, 2006 No comments

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.

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

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

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
pan> 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.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Useful Procedures Tags: , ,