VBA - Visual Basic for Applications

If you have to change the sort order of data returned in a RecordSet to behave case sensitive in VBA you can use a trick, which is provided by Microsoft.

Source Code

The below function returns a hexadecimal representation of a handed String, which can then be used in an ORDER BY clause of a sequel statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
' @Author - Microsoft
' @ChangeDate - 2007
' @Description - Returns the hexadecimal expression for a handed String,
' which can then be used in sequel statements to sort case sensitive.
' @Remarks - Original Source can be gotten from the following URL.
' https://support.office.com/en-us/article/Sort-records-in-case-sensitive-order-8fea1de4-6189-40e7-9359-00cd7d7845c0
Function StrToHex(S As Variant) As Variant
    Dim Temp As String
    Dim I As Integer
    
    If VarType(S) <> 8 Then
        StrToHex = S
    Else
        Temp = ""
        For I = 1 To Len(S)
            Temp = Temp & Format(Hex(Asc(Mid(S, I, 1))), "00")
        Next I
        StrToHex = Temp
    End If
End Function

Example

1
2
3
select * 
from aTable 
order by StrToHex(aTextField)

References

Original Source is available at following URL.
https://support.office.com/en-us/article/Sort-records-in-case-sensitive-order-8fea1de4-6189-40e7-9359-00cd7d7845c0

 

Handling of Date values can become very annoying. Especially, if Excel always thinks it knows better than the programmer or user *sigh*! Automation is meant to help people, not mess up their work.

In order to help me at least displaying date values in the correct format I implemented a little function to get a pattern string based on a users system locale settings.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
' @Author - Alexander Bolte
' @Change Date - 2013-12-14
' @Description - Determines the date format of the system the application is running on.
' The Excel application is getting the date seperator and the format of date values
' from the systems locale settings.
' @Returns - A String holding literals for Day (dd), Month (mm) and Year (yyyy)
' in the order corresponding the systems locale setting separated using the locales date separator.
Function getDateFormat() As String
    Dim dateFormat As String
    Dim datOrder As Integer
    Dim datSeparator As String
    
On Error GoTo err_handler:
    
    datOrder = Application.International(xlDateOrder)
    datSeparator = Application.International(xlDateSeparator)
    
    If datOrder = 0 Then
        dateFormat = "mm" & datSeparator & "dd" & datSeparator & "yyyy"
    ElseIf datOrder = 1 Then
        dateFormat = "dd" & datSeparator & "mm" & datSeparator & "yyyy"
    ElseIf datOrder = 2 Then
        dateFormat = "yyyy" & datSeparator & "mm" & datSeparator & "dd"
    End If
    
err_handler:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    getDateFormat = dateFormat
End Function

The following looked simple enough for me.

Source Code

Here a solution, which works for me however you have to be careful since it relies on a certain environment variable to be present. If this environment variable is not available the method automatically assumes the os to operate in 32 bit.

1
2
3
4
5
6
7
8
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-27
' @Description - Determines if a users operating system is of 64 bit or 32 bit.
' In a 32 bit windows operating system the environment variable "ProgramW6432" simply does not exist.
' @Returns true, if the default directory for 32 bit applications is C:\Program Files (x86), else false.
Function Is64bit() As Boolean
    Is64bit = Len(Environ("ProgramW6432")) > 0
End Function

Unfortunately thie below did not work for me as it returned "x86" no matter if I started it on a 64 bit or 32 bit os.

1
2
3
Sub testArchitecture()
    MsgBox Environ("PROCESSOR_ARCHITECTURE")
End Sub

 

Subcategories

This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.

Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.

This category holds articles regarding general things in MS Office VBA independent from the MS Office application.  

This category holds articles regarding Access VBA, but also general things I come accross Access and its usage in companies.