Thursday, 7 April 2011

Schedule automation In VBA


Hi
I am writing this blog to discuss about the automation in excel

First we will create a button in excel sheet and add caption to it.
I am currently giving the button name as MYCalculateButton_Click().
This will create a macro i.e. a sub function

The events generated are start and stop .
we will consider two cases when a button is click.
 1. it will call a function and change its caption to stop.this function is currently in another module
 2.When stop is clicked the automation stops

Below is the code when the button is clicked.
--------------------------------------------------------------------

Public dTime As Date
Public counter As Date

Private Sub  MYCalculateButton_Click()
    
    If EstimateVolatilityButton.Caption = "Submodule" Then
        counter = 5
        EstimateVolatilityButton.Caption = "Stop"
        dTime = Now + TimeValue("00:00:01")
        Application.OnTime dTime, "Submodule"
    Else
        If EstimateVolatilityButton.Caption = "Stop" Then
            Application.OnTime dTime, "Submodule", schedule:=False
            EstimateVolatilityButton.Caption = "MyCalculate"
        End If
    End If
    
End Sub

--------------------------------------------------------------------------------------------------------------
here we have to consider two variable namely date and counter

1.when the button is clicked then it will set the counter value to 5
2.
change its current caption name to  "STOP"
3.dTime  will set the current time and increment it to i second
4.next line will call the "SubModule" Function which is present in another module.
similary when stop is pressed it will shedule the application to false.
and set the caption name to MY Calulate

--------------------------------------------------------------------------------------------------------------------

now How to handle this counter and dtime parameter in a module

Below is the code for  Module 
--------------------------------------------------------------------------------------------------------------------
Dim CurrentRow As Long

Public Sub EstimateVolatility()
    Dim CurrentWorkbook As Workbook
    Dim VolEstimateSheet As Worksheet
    Dim WorkBookName As String
    Dim WorkSheetName As String
    
    WorkBookName = "Book1.xlsm"
    WorkSheetName = "MYsheet"
    'DataSheetName
    Set CurrentWorkbook = Workbooks(WorkBookName)
    Set VolEstimateSheet = CurrentWorkbook.Worksheets(WorkSheetName)
    
    CurrentRow = Workbooks(WorkBookName).Worksheets(WorkSheetName).counter


''Algorithm starts

MYsheet.Cells(CurrentRow, 1) = Sheet11.Cells(CurrentRow, 1)


MYsheet.Cells(CurrentRow, 2) = "NIFTY"
MYsheet.Cells(CurrentRow, 3) = Sheet11.Cells(CurrentRow, 2)

''' ALgorithm Ends


CurrentRow = CurrentRow + 1
Workbooks(WorkBookName).Worksheets(WorkSheetName).counter = CurrentRow
NextSchedule = "00:00:10"
Workbooks(WorkBookName).Worksheets(WorkSheetName).dTime = Workbooks(WorkBookName).Worksheets(WorkSheetName).dTime + TimeValue(NextSchedule)
Application.OnTime Workbooks(WorkBookName).Worksheets(WorkSheetName).dTime, "SubModule"

End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------

Firstly we are setting the current work book name and work sheet name

We are getting the counter value from the macro sheet we will place it to find the current row value
the function will apply its algorithm taking data from the another worksheet
and doing the calculation .
after the calucation end the counter value is increased to one so to point the next row
now we are declearing the NextSchedule value to 10 second
It will cause the dtime to increase to 10 second in the next line
then after 10 second the  function will automatically call itself

No comments:

Post a Comment