Using Windows API To The Worksheet in VBA

How to use Windows API funtion calls in Excel VBA programming, here lists some common used Windows APIs and explains how you can put them to work

When developing Office or Excel based applications in particular, we can get most things done by using the Office / Excel object model. Occasionally though, we need features that VBA doesn't provide. In these cases, we have to use the function calls that are available in Windows.

In the userform's code module, paste the API function declarations at the top of the module, outside of and before any Sub or Function or Property procedures.

Get Windows User Name

If you need to know who's logged into an database, use GetUserName. This API is so simple, why would you bother writing your own code? GetUserName retrieves the name of the current system or the current user logged into the network.

Private Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function apicGetUserName() As String
'Call to apiGetUserName returns current user.
Dim lngResponse As Long
Dim strUserName As String * 32
lngResponse = GetUserName(strUserName, 32)
apicGetUserName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
End Function

BringWindowToTop

This API function brings the specified window to the top. If the window is a top-level window, the function activates it.

Private Declare Function BringWindowToTop Lib "user32" _
(ByVal lngHWnd As Long) As Long

When calling the function, pass the window handle value as a Long variable.

FindWindow

The can be a bit frustrating because it requires specialized information, and if you don't get it just right, the function won't work. Specifically, you need the window's class or name, as shown in the simple call below.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Setting Focus To The Worksheet

Private Declare Function BringWindowToTop Lib "user32" _
(ByVal lngHWnd As Long) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" ( _
ByVal HWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Const WM_SETFOCUS = &H7
Private Sub SetSheetFocus()
Dim HWND_XLDesk As Long
Dim HWND_XLApp As Long
Dim HWND_XLSheet As Long
HWND_XLApp = Application.HWnd
HWND_XLDesk = FindWindowEx(HWND_XLApp, 0&, "XLDESK", vbNullString)
HWND_XLSheet = FindWindowEx(HWND_XLDesk, 0&, "EXCEL7", ActiveWindow.Caption)
SendMessage HWND_XLSheet, WM_SETFOCUS, 0&, 0&
End Sub

Private Sub SetAppFocus()
Dim HWND_XLDesk As Long
Dim HWND_XLApp As Long
HWND_XLApp = Application.HWnd
SendMessage HWND_XLApp, WM_SETFOCUS, 0&, 0&
End Sub

Function apicFindWindow(strClassName As String, strWindowName As String)
'Get window handle.
Dim lngWnd As Long
lngWnd = FindWindow(strClassName, strWindowName)
End Function

GetComputerName

GetComputerName, is similar to GetUserName except it retrieves the system's name.

Private Declare Function GetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function apicGetComputerName() As String
'Call to apiGetUserName returns current user.
Dim lngResponse As Long
Dim strUserName As String * 32
lngResponse = GetComputerName(strUserName, 32)
apicGetComputerName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
End Function

Embed MS Office in ASP.NET Program

Embedding MS Project

Embedding Visio

An Easy Way to Embed Excel in a Web Page

Embedded MS PowerPoint

Disables MS Word Standard Command

Disable Office Ribbon Button

Show/Hide Office Menu Bar

Get Started! Make your application display and interact with MS Office. Try Office Viewer Component
Top