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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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()
|
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 |
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!