By this point, most of the world has been made aware of the atrocious events that happened last Monday at the MEN arena in Manchester. For me and my family those events were very close to home in both meanings of the phrase. Continue reading “The Manchester Bombings”
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”
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.
I often see a lot of confusion around variables in VBA, especially when people see things like this:
Run-time error ‘6’: Overflow
and don’t understand why their code isn’t working. For all of you “TLDR” types, here’s a quick table of the more common data types:
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.