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:
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
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