VBA Essentials: My Clean VBA Code

A year ago at the CS knowledge competition the host asked me which language is not for programming -- I chose VBA

Posted by Danni on September 1, 2018

Truth to be told, I never wrote any VBA code before my boss told me so.

.. well, haven’t we all been in that situation before?

After a month of browsing and being roasted. I gathered my thoughts on how to write clean and workable VBA code.

Basically, a macro is a piece of code that runs in Excel environment to automate tasks. You may enable the Developer section in Excel and open Visual Basic Editor to start your Macro day. To deal with large-scale workbook/worksheet/pivot table tasks, it’s always considered to be easier to use Macro(let’s leave the argument to enthusiasts). Let’s begin now:

Important Notes

Do not loop with Dir() when Dir() is called in a sub. The function Dir uses a static iterator. Thus if you call it in a subfunction, it will initialize the one from the main function.

To solve this, you may use an array to loop through and store the Dir values or use Scripting.FileSystemObject.

While (xFile "") 

ReDim Preserve myArray(UBound(myArray) + 1) 

myArray(UBound(myArray)) = xFile 
xFile = Dir() 
Wend 

'second loop, used store array to call sub Dim n As Integer For n = 1 To UBound(myArray) 

Set wb = Workbooks.Open(Filename:=xStrPath & "\" & myArray(n)) 
'open file Call SplitData wb.Close SaveChanges:=False Next