Thursday, 14 April 2011

How to use a ActiveX VB dll in VBA


Fig1. Toolbar























---------------------------------------------------------------------------------------
To add a Active X VB dll. First go to Tool->reference ->
2.Go Browse and add the selected dll.
3.To access its class function 
----------------------------
Dll Library name:
MyCalculator
Class name: Calculator

Function Name: Add(int ,int)

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

To access this dll function:
You need to create an object of this clas s
as
Dim obj1 as New MyCalculator.Calculator
////////////////////////
To access the member function 
obj1.Add(2,3)
------------------------------------------

--------------------------------------------------------------------------
Using C dll in VBA
------------------------------------------------------------------------

To add a c dll
1. Create a module in VBA
2. declare the c dll functions
Public Declare Function Add Lib "C:\example1.dll" (ByVal FirstNum As Long, ByVal secondnum As Double) As Double.


Here Add is my function which is in the dll example1.dll
To know this functiom=n you need to add the full path of the dll and call this function by using their parameters as written in Bold above


The return Type  will be double for the function

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