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.

The following methods provide you with three different ways of reading a range into a collection in VBA.

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
59
60
61
62
63
64
65
66
67
68
69
Public Function readRangeIntoTableCollection(ByRef rng As Range, ByVal keyCol As Integer) As Scripting.Dictionary
    Dim i As Long
    Dim j As Integer
    Dim rec As Scripting.Dictionary
    Dim dict As New Scripting.Dictionary
    
    If Not (rng Is Nothing) Then
        If keyCol <> 0 Then
            ' Loop through all rows in a given range object.
            For i = 2 To rng.Rows.Count
                Set rec = New Scripting.Dictionary
                ' Loop through all columns in a given range object.
                For j = 1 To rng.Columns.Count
                    ' Read one record.
                    rec.Add rng.Cells(1, j).value, rng.Cells(i, j).value
                Next j
                ' Add one record to returned collection.
                dict.Add rng.Cells(i, keyCol).value, rec
            Next i
        End If
    End If
    
    Set readRangeIntoTableCollection = dict
End Function
 
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Reading a provided Range objects values into a collection holding key value pairs.
' @Param rng - a Range object holding one or more columns.
' @Param keyCol - a column within provided Range holding keys.
' @Param valCol - a column within provided Range holding values.
' @Remarks - The index for columns starts in a provided Range, not in the worksheet.
' If for example the handed Range starts in column C of a worksheet
' and you need the first column of handed Range as keys, you would provide 1 and not 3 as keyCol.
Public Function readRangeIntoCollection(ByRef rng As Range, ByVal keyCol As Integer, ByVal valCol As Integer) As Scripting.Dictionary
    Dim i As Long
    Dim dict As New Scripting.Dictionary
    
    If Not (rng Is Nothing) Then
        If keyCol <> 0 And valCol <> 0 Then
            For i = 1 To rng.Rows.Count
                dict.Add rng.Cells(i, keyCol).value, rng.Cells(i, valCol).value
            Next i
        End If
    End If
    
    Set readRangeIntoCollection = dict
End Function
 
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Reading a provided Range objects values into a collection.
' @Param rng - a Range object holding one column.
' @Returns a collection of type VBA.Collection holding all values
' from the first column in provided Range object.
Public Function readRangeIntoVbaCollection(ByRef rng As Range) As VBA.Collection
    Dim i As Long
    Dim dict As New VBA.Collection
    
    If Not (rng Is Nothing) Then
        For i = 1 To rng.Rows.Count
            If rng.Rows(i).Hidden = False Then
                dict.Add Trim(rng.Cells(i, 1).value)
            End If
        Next i
    End If
    
    Set readRangeIntoVbaCollection = dict
End Function

 

 

Rarely I've come across the following problem in Excel VBA. 

Some source code is definitely correct, but it just won't compile. I could one time confirm different Excel versions to be the root cause of the issue. Even though I slightly adjusted the source code in the target version of Excel, the compiler still showed funny error messages.

The trick that did solve the issue in the end was to cut the whole source code, compile and save the empty source code objects (modules, classes, etc..), paste the source code into target objects again and then compile as well as save in the target version of Excel.

From my understanding the file still held bits of the older version of Excel. However, I experienced this also using the same version of Microsoft Access on the same machine. Cutting and then pasting the source code with saving in between did the trick again. 

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.