Wednesday, August 25, 2010

Using DLL in Excel VBA macro

1. Hook up DLL with VBA

a. Start Excel and open up VBA Editor
- After starting your Excel file, press "Alt + F11" to start VBA editor

b. Add a module
- "Insert" -> "Module" to add a new module.

c. Write declaration code to hookup dll with VBA
- Declaring a VBA function to call a DLL goes like this.
[Public | Private] Declare Function name Lib "libname" [Alias "aliasname"] [([arglist])] [As type]

- In your Module file, add the following.

'Declare all the API-specific items Private to the module
Private Declare Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long

Private Const SM_CXSCREEN = 0 'Screen width
Private Const SM_CYSCREEN = 1 'Screen height

'The width of the screen, in pixels
Public Function ScreenWidth() As Long
ScreenWidth = GetSystemMetrics(SM_CXSCREEN)
End Function

'The height of the screen, in pixels
Public Function ScreenHeight() As Long
ScreenHeight = GetSystemMetrics(SM_CYSCREEN)
End Function

2. Using VBA code in your excel.

1 comment:

Bhagya said...

Hi.. I have a dll file called square.dll. How do I call this in VB? I did not understand where the dll is called from your example. Please help me