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