VBA Tips & Tricks

I recently saw a post in a LinkedIn group where someone had discovered that certain VBA string manipulation methods actually return a Variant type instead of a String type (We’ll get back to this shortly).

This made me think – I wonder what other small things people aren’t aware of with VBA that could improve their office programming. So here’s just a few tips that might speed up your code or hopefully just give you a better understanding of how to design your code to get better performance or readability:
Continue reading “VBA Tips & Tricks”

When VBA isn’t VBA

How to list all files in all subfolders using VBA…

This is something that I see asked quite a lot on various forums, and I’ve seen lots of different attempts using recursion and the file system object to name but a few. I’m going to make it really simple for you – it looks like this:

files = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR ""%USERPROFILE%\Desktop\*.*"" /S /B /A:-D").StdOut.ReadAll, vbCrLf), ".")

Okay, so that might not look simple (and what the hell is that smiley face in the middle of it?). That’s because it’s not technically VBA.

Continue reading “When VBA isn’t VBA”

How to get your questions answered online

Okay, so you may think that this is obvious but in fact I feel like I’m seeing more and more people asking questions that quite frankly, I will refuse to answer based purely on the first few opening words.

“That’s just really narrow minded” I hear you cry. You may think so, but if I’m being brutally honest – I can afford to be at this point – after all, you’re the one that came looking for help, not me.

Aside from some of the classic opening statements (which I’ll get into shortly) if your question looks something like this then you’re not going to get help from me.

Continue reading “How to get your questions answered online”