VBA - Visual Basic for Applications

If you have to create and send an email automatically, the following method might help.

I have not tested it with HTML yet, but will do that at some point.

The method requires MS Outlook to be installed, since it is automating Outlook  in order to create an email with attachment.

Hits: 11704

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: 11066

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
Hits: 11109
  1. Get Systems Full User Name
  2. Determine architecture (64 or 32 Bit) in VBA
  3. Link MS Access tables using VBA
  4. Join tables using LIKE

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