Knowledge Base Articles

It's always nice when you go to a forum and someone gives you a nice bit of VBA code that is supposed to accomplish your goals. But if you've never used VBA code before, it's kind of hard to know what to do with it! This article has been written to get you up and running and get that code in the right place.

Please note... this article assumes you've been directed to add your code to a standard module, as 99% of code is housed there. If your coder told you to put your code in a worksheet module or the ThisWorkbook module, this wont' quite get you there. (You should still read this article, but also this one which lists the other types of Excel modules.)
...

Over the past while we’ve been building a Dashboard report for our golf course. It’s got some historical information in it, but we’ve also pulled in things like weather forecasts. The intention is that our managers will be able to see where we’ve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

We put out a prototype of the Dashboard report, and our Director of Golf said “This is cool. What’s the chance we could also have the events coming up over the next week listed?”
...

The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for an item given the ID of the product.

But VLOOKUP is more than just that; it is the gateway to real Excel knowledge. The VLOOKUP function contains everything that a function can throw at you: multiple required parameters, optional parameters with defaults, and needs both ranges and numeric data in its input strings. If you can master this function, you can master ANY other function in Excel.
...

Introduction:
There are actually two different ways to accomplish this task, depending on exactly what you want to do. You can:

• Connect to an existing application (uses the GetObject method)
• Create a new application with no document loaded (uses the CreateObject method)

Please note that while all of this code was written to control Microsoft Word from Excel, you can use it in any other VBA enabled application, and you can also use it to control any other VBA enabled application, simply by changing "Word" to Excel, Powerpoint, Access, etc... in the examples below.
...

Introduction:
This macro can be used to count how many files exist in a folder and, if passed the file extension, can also be used to count only files of a specific type. (For example "xls" files.) I've actually published two articles along this vein at VBA Expresss, but this one uses the File System Object (some know it as the File Scripting Object, or just FSO,) to do it. The biggest reason for the re-write is that I needed to use this in an Excel 2007 instance, which does not support the FileSearch method.
If you are looking for a version to count the files in a folder AND subfolders, then look here.
...

I can't claim the original idea for this article. The concept actually comes from Professional Excel Development, but I developed the code, such as it is here, on my own. The reason for this was because I couldn't find it in the book when I went looking for it, so I knocked up my own version, and decided to post that here.

This is kind of a neat little routine, which adds a "DebugMode" property to the ThisWorkbook module. It also checks the MS Office username every time someone opens the file, and if it's me, it asks me if I want to use Debugging Mode. ...

I recently had reason to create a database on the fly if one did not exist. Since it took me some trial, error and searching (and then more trial and error,) I decided to share the method to do this. The following routine will create an Access database from any VBA enabled application, such as Word, Excel, Outlook, etc...
...

I’m a huge consumer of Data Validation in Excel. At this point, however, Excel services (the Excel webApp) is still so new that there are very few of the techniques that we normally use in Excel which are web compliant. This article looks at ways that we can implement data validation into an Excel services solution.
...

I've received several comments on my blog that people dislike Excel 2007's Compatibility Checker, and would like to turn it off globally. While it's true that you can turn it off for each workbook, the main user interface seems devoid of a way to actually tell Excel that you never want to check your workbooks for compatibility when saving to 2003 or earlier versions. ...