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 following method exports all available queries in an Access database into individual text files, which are called like the corresponding query.

Source Code

DAO does not provide the support ADODB gives you, therefore this method will only work with queries created and stored in a current MS Access database.

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
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-15
' @Description - exports all queries available in current database query definitions.
' @Param trgDir - a String representing a target directory to export
' all queries in current query definition.
' @Returns true, if all queries have been successfully exported into provided target directory.
Function exportQueries(ByVal trgDir As String) As Boolean
    Dim sql As String
    Dim name As String
    Dim q As Object
    Dim trgFile As String
 
    ' Reference a query.
    For Each q In CurrentDb.QueryDefs
        ' Get the SQL from a referenced query as text.
        sql = q.sql
        ' Get a queries name.
        name = q.name
        ' Replace special characters in file name.
        trgFile = trgDir & "\" & VBATools.replaceSpecialCharacters(name) & ".sql"
        ' Delete the target file, if already existing.
        Call VBATools.deleteFileOnHD(trgFile)
        ' Write the query text into a separate text file.
        Call VBATools.writeLineToTextFile(trgFile, sql, False)
    Next
End Function

Resources

The method writeLineToTextFile is not a standard VBA method, but can be found at following URL.

The target encoding should not be UTF-16LE but ASCII, if you intend to use a versioning tool like GIT to keep track of changes in MS Access queries.

Write a String into a text file

Replacing special characters can be a pain, if you do not rely on regular expressions.

Replace special escape characters in String

Delete a file on a users hard disc (VBScript, which can easily be adjusted to VBA).

VBScript to delete file

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.