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

 

Hits: 9386

The below snippet will provide you with a systems full user name.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2014-10-13
' @Description - Returning the system user name.
Public Function getFullUserName() As String
    Dim WSHnet  As Object
    Dim UserFullName As String
    Dim userName As String
    Dim UserDomain As String
    Dim objUser As Object
    
    Set WSHnet = CreateObject("WScript.Network")
    userName = WSHnet.userName
    UserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & UserDomain & "/" & userName & ",user")
    UserFullName = objUser.FullName
    
    Set WSHnet = Nothing
    Set objUser = Nothing
    
    getFullUserName = UserFullName
End Function

Referenced APIs

Since the source is using late binding it is not necessary to set a reference in the VBA Editor for a specific version of the referenced API named "WScript".

Hits: 9247

Below method allows you to remove the shadow from all shapes of the same type (arrow, rectangle, ... ) at once, instead of selecting each shape manually.

If you want to apply more formats than only removing a shadow, then use the methods PickUp and Apply of the Shape object.

PickUp copies the format of one Shape object and Apply copies a before picked up format.

Source Code 

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
' @Author - Alexander Bolte
' @ChangeDate - 2014-11-27
' @Description - removing a shadow from all objects of type Shape 
' of the same type (arrow, rectangle, ...) in current slides.
' Select one shape e.g. an arrow in a power point presentation and execute this code.
' The method iterates through all shapes in current ShapeRange and removes the 
' shadow style from all shapes of the same type.
Sub PickItUp()
    Dim oSld As Slide
    Dim oSelShp As Shape
    Dim oShp As Shape
    Dim lType As Long
 
    On Error GoTo err_handle:
 
    ' Results are unpredictable if you start with
    ' more than one shape selected.
    If ActiveWindow.Selection.ShapeRange.Count <> 1 Then
        MsgBox "Select only one shape", vbExclamation, "Power Point"
	Exit Sub
    End If
 
    ' set a reference to the selected shape
    Set oSelShp = ActiveWindow.Selection.ShapeRange(1)
 
    With oSelShp
        ' pick up its formatting
        .PickUp
        ' store its type
        lType = .Type
 
        ' Exclude placeholders
        If lType = 14 Then
            Exit Sub
        End If
 
        If .Fill.Type = msoFillPicture Then
            Exit Sub
        End If
 
        For Each oSld In ActivePresentation.Slides
            For Each oShp In oSld.Shapes
                If oShp.Type = lType Then
                    If oShp.Fill.Type <> msoFillPicture Then
                        'oShp.Apply
                        oShp.Shadow.Visible = msoFalse
                    End If  ' <> msoFillPicture
                End If  ' Type - lType
            Next oShp
        Next oSld
    End With    ' oSelShp
 
err_handle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    MsgBox "Uncatched exception.", vbCritical, "Power Point"
End Sub
Hits: 9297
  1. Link MS Access tables using VBA
  2. Determine architecture (64 or 32 Bit) in VBA
  3. Join tables using LIKE
  4. Getting Date format based on locale settings

Subcategories

Excel VBA Article Count:  30

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.

MS Office and VBA Article Count:  11

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

Power Point VBA Article Count:  1

Access VBA Article Count:  7

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

Access VBA DAO Article Count:  2