VBA - Visual Basic for Applications

The below method will replace all special or escape characters in a provided String.

This is of use, if you have to use before unknown Strings from any source for example in a file name.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
' @Author - Alexander Bolte
' @Description - replaces all characters, which match the pattern "[^A-Za-z0-9-_ ]" in a given String.
' @Param aText - a String in which invalid characters should be replaced.
' Returns the handed String from aText excluding all replaced characters.
Public Function replaceSpecialCharacters(ByVal aText As String) As String
    Dim regEx As New RegExp
    Const cReplEscapeChar As String = "[^A-Za-z0-9-_ ]"
    
    regEx.pattern = cReplEscapeChar
    regEx.Global = True
    regEx.IgnoreCase = True
    aText = regEx.Replace(aText, "")
    
    replaceSpecialCharacters = aText
End Function

Enjoy. 

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".

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

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.