VBA is extremely underestimated programming language. I have always been thinking that there are almost no real #VBA developers, because the “real” developers do not want to do some meaningless “macro” stuff and the people who work with #VBA are only some advanced controllers or analysts. However, usually such professionals only use VBA for some small tasks and thus they never go too deep into programming. So, to summarize – those who can learn VBA and make a change in the language somehow despise it and those who want to learn it within their daily job somehow lack enough IT knowledge.
Thus, I am really happy every time I see something new showing up in the #VBA world and thus trying to bring the language one step further to the group of the nice guys. 🙂
This article reviews the AddIn AutoMacro accessible from this link – automateexcel.com/vba-code-generator
This is what you get in your VBE ribbon after activating it:
If you click on “Procedure” you would get this form:
genereating this code once you click OK:
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
Array Builder looks like this:
And produces this piece of code:
ReDim Preserve arr(0 To 100)
Dim arr() As Variant
ReDim arr(1 To 10)
Dim rng As Range
Dim cell As Range, cellIndex As Integer
Set rng = Range("A1:A10")
For Each cell In rng.Cells
cellIndex = cellIndex + 1
arr(cellIndex) = cell.Value
When we go further to the right of the ribbon, we see the following options:
- Turn on and off comments. One button, usually on the VBEditor there are 2 – one for comment and one for uncomment.
- Line numbers. If you are some old school programmer, then it could be useful. Or if you want to know which line has failed of your application and send some email to someone.
- Sort procedures – pretty much does what it says – gives a window and sorts the procedures.
- AutoIndent – Indents the whole code only in the active module/worksheet.
- Remove Excess line breaks – removes any new lines, if they are more than 2.
- Close All Windows and Close All Windows except Active – these are self explanatory;
The third part of the AddIn gives predefined code snippets, sorted by groups:
From the standard IF to not so standard Files. These are 3 functions, which I have decided to show in the article:
Function FileNameFromPath(strFullPath As String) As String
FileNameFromPath = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "\"))
'Get File Name from Path (without extensions)
Function FileNameFromPath_NoExt(strFullPath As String) As String
Dim nStartLoc As Integer, nEndLoc As Integer, nLength As Integer
nStartLoc = Len(strFullPath) - (Len(strFullPath) - InStrRev(strFullPath, "\") - 1)
nEndLoc = Len(strFullPath) - (Len(strFullPath) - InStrRev(strFullPath, "."))
nLength = intEndLoc - intStartLoc
FileNameNoExtensionFromPath = Mid(strFullPath, nStartLoc, nLength)
Dim oApp As Object
Dim Fname As Variant
Dim Output_Folder As Variant
Dim strDate As String
Dim i As Long
'Select multiple zip files to unzip
Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
If IsArray(Fname) = False Then
'Set output folder path for unzip files
Output_Folder = "C:\Unzip"
'Append backslash to output folder path
If Right(Output_Folder, 1) <> "\" Then
Output_Folder = Output_Folder & "\"
'Extract the files into output folder
Set oApp = CreateObject("Shell.Application")
For i = LBound(Fname) To UBound(Fname)
MsgBox "Files successfully extracted to: " & Output_Folder
The last part of the AddIn is an option for saving your own CustomCode. Which could be quite useful, if you have your own snippets.
As I said, I am really happy to blog about any new #VBA “thing”. This means that #VBA is not dead and the bad jokes of R.Martin would simply stay bad jokes:
And that’s all folks!