Category: Windows API

Windows API Viewer for MS Excel

Posted on by Dick Kusleika

This is a guest post by Dennis Wallentin.

The Tool

Windows API Viewer for MS Excel is a standalone, powerful tool for creating Windows API code, with or without conditions, to be inserted into code modules. It offers several lists of APIs for both platforms, x86 and x64, that can easily be used in various solutions. If wanted, it can create conditional Windows API solutions. The two conditions that can be applied are Win64 and VBA7. More information about them can be found in the help files shipped with the tool. The tool is shipped with extensive help support; the help includes help files and web pages that are relevant about Windows API.

The following screen shot shows the main form:

It is very easy to use Windows API Viewer. Select the code options you want and then export them to the clipboard. That’s all! Since it’s a standalone tool it can be used with other development tools. But the Windows APIs here are selected for MS Excel in particular. The tool’s help file explains and shows the various output you can create. Before the code solution is exported to the clipboard you can preview it as the following screen shot shows:

If you want further information about the selected Windows API the tool offers use of Google to find out more as the below screen shot shows:

Download

The Windows API Viewer for MS Excel is available in x86 version and in x64 version. To install it just double click on the downloaded exe file and follow the instructions on the screen.

Windows API Viewer – x86 Version
Windows API Viewer – x64 Version

Requirements

Since Windows API Viewer for MS Excel is not an add-in to MS Excel it can be used with whatever Excel version available. It can also be used with other development tools as well. However, the following requirements must be met in order to use the tool:

  1. Windows XP or later version.
  2. Microsoft.NET 4.0 or later version.

Development tools

The following tools have been used when creating Windows API Viewer for MS Excel:

  1. Microsoft Visual Studio 2015
  2. DevExpress WinForm Controls
  3. SQLite
  4. Help + Manual
  5. SamLogic Visual Installer 2015
  6. BoxedApp Packer

The Source Files

The Windows API sources come from two different text files by Microsoft. Unfortunately, the file for x86 Windows API is rather old, released by Microsoft in 1998. The other file was released when Excel 2010 was released and includes the x64 versions of Windows API. For obvious reason they are not complete. The quality has been improved as I have been forced to manually work with these files. All the Windows API are now stored in a SQLite database and can easily be updated when necessary. If you find some error or you see that some APIs are missing, please send me an e-mail and I will update accordingly. New versions of the tool will then be made available. By the way, did I mention that working with these files has been tedious?

License

The Windows API Viewer for MS Excel is made available based on the MIT License (MIT).

Home Page

Windows API Viewer has its home at Ron de Bruin’s site where upgrades and news will be published:
http://www.rondebruin.nl/win/dennis/windowsapiviewer.htm

Special Thanks
Special thanks goes to Ron de Bruin and Ken Puls.

All the very best,
Dennis

Daylight Saving Time Error

Posted on by Dick Kusleika

It’s DST here in the US and I couldn’t be happier. I don’t care if my drive to work is pitch black, but the drive home? That’s another story.

One thing I learned since the clocks sprung forward is that the GetTimeZoneInformation API doesn’t work the way I thought. The TIME_ZONE_INFORMATION return type has a Bias property. Bias tells you how many minutes you are away from GMT. Or so I thought. It actually tells you how many minutes you are from GMT in standard time. The TIME_ZONE return value tells you if it’s daylight saving time or standard time. So you have to take both into account to get the correct time.

Here’s the API declaration

Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(0 To 31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(0 To 31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

''''''''''''''''''''''''''''''''''''''''''''''
' These give symbolic names to the time zone
' values returned by GetTimeZoneInformation .
''''''''''''''''''''''''''''''''''''''''''''''

Private Enum TIME_ZONE
TIME_ZONE_ID_INVALID = 0 ' Cannot determine DST
TIME_ZONE_STANDARD = 1 ' Standard Time, not Daylight
TIME_ZONE_DAYLIGHT = 2 ' Daylight Time, not Standard
End Enum

Private Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)

And the updated procedure:

Public Function ConvertTimeToLocal(ByVal dtTime As Date, ByVal sZone As String) As Date

Dim tzi As TIME_ZONE_INFORMATION
Dim tz As TIME_ZONE
Dim lGmtOff As Long

tz = GetTimeZoneInformation(tzi)

Select Case UCase(sZone)
Case "EDT"
lGmtOff = -4
Case "EST", "CDT"
lGmtOff = -5
Case "CST", "MDT"
lGmtOff = -6
Case "MST", "PDT"
lGmtOff = -7
Case "PST"
lGmtOff = -8
Case vbNullString
lGmtOff = -tzi.Bias / 60
End Select

If tz = TIME_ZONE_DAYLIGHT Then lGmtOff = lGmtOff - 1

ConvertTimeToLocal = dtTime - (TimeSerial(0, tzi.Bias, 0) + TimeSerial(lGmtOff, 0, 0))

End Function

I also added a UCase around the zone because it’s just stupid not to have that. Enjoy saving the daylight, but remember you’ll owe it back this fall.

Update:

That’s why we write tests people.

Public Function ConvertTimeToLocal(ByVal dtTime As Date, ByVal sZone As String) As Date

Dim tzi As TIME_ZONE_INFORMATION
Dim tz As TIME_ZONE
Dim lGmtOff As Long
Dim lBias As Long

tz = GetTimeZoneInformation(tzi)

lBias = tzi.Bias
If tz = TIME_ZONE_DAYLIGHT Then lBias = lBias - 60

Select Case UCase(sZone)
Case "EDT"
lGmtOff = -4
Case "EST", "CDT"
lGmtOff = -5
Case "CST", "MDT"
lGmtOff = -6
Case "MST", "PDT"
lGmtOff = -7
Case "PST"
lGmtOff = -8
Case vbNullString
lGmtOff = -tzi.Bias / 60
If tz = TIME_ZONE_DAYLIGHT Then lGmtOff = lGmtOff + 1
End Select

ConvertTimeToLocal = dtTime - TimeSerial(lGmtOff, 0, 0) - TimeSerial(0, lBias, 0)

End Function

Public Sub Test_ConvertTimeToLocal()

Dim dtTestTime As Date

dtTestTime = TimeSerial(9, 46, 13)

Debug.Assert ConvertTimeToLocal(dtTestTime, vbNullString) - TimeSerial(9, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "CDT") - TimeSerial(9, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "EST") - TimeSerial(9, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "EDT") - TimeSerial(8, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "CST") - TimeSerial(10, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "MDT") - TimeSerial(10, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "MST") - TimeSerial(11, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "PDT") - TimeSerial(11, 46, 13) < TimeSerial(0, 0, 1) Debug.Assert ConvertTimeToLocal(dtTestTime, "PST") - TimeSerial(12, 46, 13) < TimeSerial(0, 0, 1) End Sub

An MSForms Treeview

Posted on by jkpieterse

If you have ever used the Treeview control from the “Additional controls” section, then you know what a versatile control this is to show hierarchically organized data.

There are a couple of problems with this Treeview control:

  1. Compile errors due to a difference in how the control libraries are registered in 32 bits Windows’ System32 and 64 bit Windows’ SysWOW32 folders. If you distribute a file that was saved in 64 bit Windows, containing one of the “Microsoft Windows Common Controls 6.0” (The Treeview control is one of them) and with the reference set to “mscomctl.ocx”, people using 32 bit Windows will almost certainly have problems. At best it could entail removing both the control and the reference and replacing both, but at worst the user’s Excel can crash when trying to load the file and run the app.
  2. The standard Treeview control, like all non built-in ActiveX controls, cannot be used in 64 bit versions of Office.

Especially the second point convinced me it is time to develop a custom-made Treeview “control”, that only uses the native Office forms controls. I started building this a couple of weeks ago and after some time I tricked Peter Thornton into helping me with it :-)

The screenshot below shows both our new Treeview (left) and the Windows one (right) side-by-side:

Not bad, right?

Both Treeviews allow for checkboxes:

And both allow icons (windows Treeview not shown here):

You can also edit a node:

And expand and collapse nodes and navigate the tree using your arrow keys.

We built the custom Treeview using just two class modules. Using it in your project will require nothing more than copying the two classes and adding a bit of plumbing to your userform: some code and an empty frame which will hold the Treeview and possibly a frame with pictures for the icons.

We’re currently doing some cleaning up (like removing obsolete debugging stuff, adding comments and the like), so the “control” is not quite ready to be released to the outside world. Keep an eye on this blog, once we’re done we’ll post here.

Well, what do you think, is this useful or what? What functionality would be critical for you? Let us know!

Regards,

Jan Karel Pieterse

Excel 2013, SDI and modeless userforms

Posted on by jkpieterse

Hi Everyone,

With Excel 2013 we also got new document windowing in Excel; Microsoft decided to make Excel behave the same as Word:

from MDI:

Excel 2010 MDI interface showing two workbooks
The Excel 2010 MDI interface

to SDI:

Excel 2013 SDI interface showing two workbooks
The new SDI interface of Excel 2013

This causes havoc when one shows a modeless userform which should stay on top of all Excel windows:

Excel 2013 SDI can cause a userform to disappear
Excel 2013 SDI can cause a userform to disappear

I’ve devised a way to prevent this problem and written an article about how this was done.

Enjoy!

Regards,

Jan Karel Pieterse

www.jkp-ads.com

 

 

 

Find Matching Data in Array Speed Test

Posted on by Dick Kusleika

JP has a good post about finding exact matches in arrays. I use a similar method. I Join the array with delimiters around all the values, then use Instr to see if it’s there. Here’s my code:

Function IsInArrayDK(vArr As Variant, sValueToCheck As String, _
Optional bMatch As Boolean = True) As Boolean

Dim bReturn As Boolean
Dim sWordList As String

Const sDELIM As String = "|"

'See if it's a match even if only a substring
bReturn = UBound(Filter(vArr, sValueToCheck)) > -1

'If a match and need exact
'If exact match not needed, the line above provides the return value
If bReturn And bMatch Then
'put pipes around all the values
sWordList = sDELIM & Join(vArr, sDELIM) & sDELIM
'See if the values with pipes is there
bReturn = InStr(1, sWordList, sDELIM & sValueToCheck & sDELIM) > 0
End If

IsInArrayDK = bReturn

End Function

To test, I filled an array with 100,000 random strings, picked one of the strings to find, then timed JP’s funciton, my function, and the non-optimized method. The non-optimized method simply loops through the array and checks for values.

Function IsInArrayLoop(vArr As Variant, sValueToCheck As String, _
Optional bMatch As Boolean = True) As Boolean

Dim bReturn As Boolean
Dim i As Long

For i = LBound(vArr) To UBound(vArr)
If bMatch Then
If vArr(i) = sValueToCheck Then
bReturn = True
Exit For
End If
Else
If InStr(1, vArr(i), sValueToCheck) > 0 Then
bReturn = True
Exit For
End If
End If
Next i

IsInArrayLoop = bReturn

End Function

The code to fill the array converts Rnd to a string and puts it in the array. Then I pick one of the values (first, middle, and last) as the value I want to check.

Sub FillArray(ByRef vArr As Variant, ByVal lPlace As Long, ByRef sValue As String)

Dim i As Long

For i = 1 To 100000
vArr(i) = CStr(Int(Rnd * 10000000))
If i = lPlace Then
sValue = vArr(i)
End If
Next i

End Sub

I used the same API timer that JP uses when he does speed tests.

Public Declare Function timeGetTime Lib "winmm.dll" () As Long

And finally, the sub to test loops through the early, middle, and late values-to-check and times them.

Sub TestArray()

Dim aNames(1 To 100000) As Variant
Dim i As Long
Dim bResult As Boolean
Dim lStart As Long, lEnd As Long
Dim sValueToCheck As String
Dim aPlace(1 To 3, 1 To 2) As Variant
Dim sTable As String, sRow As String

'name the tests and determine where the value to check is in the array
aPlace(1, 1) = "Value Early:": aPlace(1, 2) = 1
aPlace(2, 1) = "Value Middle:": aPlace(2, 2) = 50000
aPlace(3, 1) = "Value Late:": aPlace(3, 2) = 99999

'The results go in an html table
sRow = Tag(Tag("Milliseconds", "td") & Tag("JP", "td") & Tag("DK", "td") & Tag("Loop", "td"), "tr") & vbNewLine
sTable = sRow

For i = 1 To 3
sRow = Tag(aPlace(i, 1), "td")
FillArray aNames, aPlace(i, 2), sValueToCheck

lStart = timeGetTime
bResult = IsInArrayJP(aNames, sValueToCheck, True)
lEnd = timeGetTime
sRow = sRow & Tag(lEnd - lStart, "td")

lStart = timeGetTime
bResult = IsInArrayDK(aNames, sValueToCheck, True)
lEnd = timeGetTime
sRow = sRow & Tag(lEnd - lStart, "td")

lStart = timeGetTime
bResult = IsInArrayLoop(aNames, sValueToCheck, True)
lEnd = timeGetTime
sRow = sRow & Tag(lEnd - lStart, "td")

sTable = sTable & Tag(sRow, "tr") & vbNewLine
Next i

Debug.Print Tag(sTable, "table", , True)

End Sub

The results:

Milliseconds JP DK Loop
Value Early: 53 53 0
Value Middle: 48 53 11
Value Late: 49 54 22

 
JP’s and mine are a wash and the loop is fastest. I guess I should just use that.

Copy Chart as a Picture

Posted on by Rob van Gelder

I needed to copy a chart to a picture, but I wanted it to be an enhanced metafile (EMF) which is kind of like a vector graphic picture format. EMF graphics scale well when the page resizes.

A user would select the chart, run the macro and a dialog would ask them where to save the picture to – pretty simple, but handy!
It uses the clipboard to do the conversion.

Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long
Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
Declare Function EmptyClipboard Lib "user32" () As Long
Declare Function CopyEnhMetaFileA Lib "gdi32" (ByVal hENHSrc As Long, ByVal lpszFile As String) As Long
Declare Function DeleteEnhMetaFile Lib "gdi32" (ByVal hemf As Long) As Long

Const CF_ENHMETAFILE As Long = 14
Const cInitialFilename = "Picture1.emf"
Const cFileFilter = "Enhanced Windows Metafile (*.emf), *.emf"

Public Sub SaveAsEMF()
Dim var As Variant, lng As Long

var = Application.GetSaveAsFilename(cInitialFilename, cFileFilter)
If VarType(var) <> vbBoolean Then
On Error Resume Next
Selection.Copy

OpenClipboard 0
lng = GetClipboardData(CF_ENHMETAFILE)
lng = CopyEnhMetaFileA(lng, var)
EmptyClipboard
CloseClipboard
DeleteEnhMetaFile lng
On Error GoTo 0
End If
End Sub