VBA – AutoMacro AddIn – VBA Software Review

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:

Sub Name()
'Description: SomeName
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.DisplayAlerts = False
Application.EnableEvents = False



endmacro:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox "Complete"
End Sub

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
    'cell.Value
    cellIndex = cellIndex + 1
    arr(cellIndex) = cell.Value
Next cell

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:

FileNameFromPath()
FileNameFromPath_NoExt()
Unzip_Files()

Function FileNameFromPath(strFullPath As String) As String
    FileNameFromPath = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "\"))
End Function

'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)
End Function

Sub Unzip_Files()
    
    'Declare Variables
    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", _
    MultiSelect:=True)
    If IsArray(Fname) = False Then
        'Do nothing
    Else
        '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 & "\"
        End If
        
        'Extract the files into output folder
        Set oApp = CreateObject("Shell.Application")
        For i = LBound(Fname) To UBound(Fname)
            
            oApp.Namespace(Output_Folder).CopyHere oApp.Namespace(Fname(i)).items
            
        Next i
        
        MsgBox "Files successfully extracted to: " & Output_Folder
    End If
    
End Sub

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!

VBA – AutoMacro AddIn – VBA Software Review

Related posts: