VBA - Visual Basic for Applications

It has been always annoying me that I could not select more than one column in the Excel Text Import Wizard / Assistant.

I had to select single columns in a preview window and then set a fields data type one by one even if all fields were of the same data type.

Using the Shift key you are able to work around this bug in the text import assistant.

Select the first column then press and hold shift key down and select a column to the right or left of the before selected column.

All columns in between the two selected will be selected as well and are available for property editing :0).

I am Mr. Forgetful. What was this password again?

Anyway, if you do not know any better, use brute force!

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
' @Author - Alexander Bolte
' @ChangeDate - 2013-12-15
' @Description - Removes the password from a protected worksheet using brute force.
' Select a protected worksheet and start the sub.
Sub PasswordBreaker()
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    Dim trg As Worksheet
  
    On Error Resume Next
  
    Set trg = ActiveSheet
  
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
        trg.Unprotect Chr(i) & Chr(j) & Chr(k) & _
            Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
            Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
        If trg.ProtectContents = False Then
            MsgBox "One usable password is " & Chr(i) & Chr(j) & _
              Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
              Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n), vbInformation, ";0)"
            Exit Sub
        End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
End Sub

If you have to get the last column in an Excel worksheet, the following method can assist since you cannot rely on the UsedRange of Excel.

Why can't you rely on the UsedRange? Read below on how to get the last row in an Excel worksheet.

Determining last row with data in an Excel Worksheet

Source Code

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.