VBA Stuff Page
Regular Expressions -- Not the irregular ones your professors warned you about!
In a nut shell, a regular expression is a pattern that describes a section of text. If you haven't learned about regular expressions then I highly recommend
you take the tutorial at Regular-Expressions.info. When
I'm testing my regular expressions I use their Regular
Tools for getting into the regex work.
I made a VBA module for performing regex work. I also reference a
Word document by D. Brett as it contains information on
binding the RegExp Object and has the pattern table with descriptions. Very helpful.
Get the last row.
Does just that, puts the number of the row where the last row of data is:
Dim LastRow As Integer
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Red, Green, Blue to Long Number translator.
This VBA module will accept a RGB value and return a Long value.
I can't remember exactly why I made this except that I needed a color from one location to paint a Shape the same color. Eh.
Capture a user's Windows Login name. -- Just don't let it escape out the door.
Some of the applications I built would have special capabilities for specific users. To easily figure out who's running the program, simply
grab their user login name.
Quick Query/Pivot Information Add-in.
This query add-in places a menu item in the right mouse-click menu. If the query
is a Query table then the menu item will be "Query Information" or if it's a Pivot table then it will display "Pivot Information".
Selecting the menu item will display a user form with the specific information. If you change the text in a box and double-click the text box label, it will
change the value for that particular item of the query.
Text box titles in bold are the parts of the query the add-in found and are changeable. For example, you could change the Command text in the box then
double-click the Command text: label to make the change to the underlying query. This won't always work for all underlying queries, for example, queries
that point to an ODBC data source that you don't have a defintion for. Even still, this is a good way to simply see what's going on inside a particular query.
Putting pivots under the light.
Many years ago at one job I had a very big workbook (created by someone else) with a lot of pivot tables but only a couple data sources. To help
reduce the size I wanted to have similar pivot tables pull their data from just the source pivot. So
I built this to help me investigate all of the pivot tables in the workbook. I also
added query table investigation as well.
To use it, Import the module and run the Run_PPI macro for getting pivot table info and run the Run_PQI
macro for getting query table info. The results will be output as a text file to the C:\ directory as "PPI.log". If you are comfortable with VBA
functions then you can change the macros to pull just some of the info as I put Optional parameters in the functions.
Go to sleep (pause execution).
This tiny executable is something I created in Thompson's AWK back in my Intel days and allows a
batch file -- or any file that calls it for that matter -- to pause for a certain length of time. In a batch file you'd simply call it (with the proper file
path if necessary) with a space after it and a number. The number represents the number of seconds you want to wait before continuing. For example:
REM Example using sleep.exe
REM Pause execution for 4 seconds then resume
REM Continue on...
Nothing spectaculer, I know, but it sure was helpful when I was automating reports and needed to insure the first process completed before starting the
next without having to do any special coding.
Converting columns to values and back again.
Here are two VBA functions that I've used several times while making custom reports for customers. This function will convert a value to a column. Simply pass it a number and it will return a letter that designates the appropriate column.
Dim sColumn as String
sColumn = ConvertValue2Column(5)
sColumn will equal the letter "E". Likewise, passing it the number 27 will return "AA". Neat, huh?
Conversly, we can convert a column to a value. Simply pass it a string that is
the column header and it will return an integer. For example,
Dim iColumn as Integer
iColumn = ConvertColumn2Value("E")
iColumn will equal 5. Similarly, passing it "AA" will return 27.
Two column VLOOKUP.
If you do a lot of reporting in Excel then you likely use the VLOOKUP function. But how many times have you wished you could use two values to determine the
lookup? Well I have and I found a neat little function by Andy Wiggins called VLOOKUP2.
This is the syntax: VLOOKUP2(lookup_value1, lookup_value2, table_array, col_index_num). Instructions are in the .bas module
but it's pretty darn simple; instead of ONE value used for the lookup you now have TWO. Andy, I salute you!
Random breaks when running VBA? -- HEY! I didn't tell you to stop there.
Do you sometimes have the issue where your code is merrily chunking along, doing code and suddenly it just stops on a line of code, for seemingly no reason?
No STOP, no breakpoint, the execution just stops! Then you have to exit Excel completely, sometimes you even have to reboot your machine...not fun and
certainly doesn't work well for automation projects. Well, I have no idea why this happens but here's a work-around to stop it. At the top of your main module, below your DIM statements, place this little nugget:
Application.EnableCancelKey = xlDisabled
That's all there is to it. The downside is that you can't use the Esc key to stop your code while it's running. One way around this is to open the VBE and
click the Reset button on the Debug toolbar (it's the blue square). When you want use of the Esc key use the following line to turn it back on:
Application.EnableCancelKey = xlInterrupt
For additional help with this command please use the Excel Help function on it. But as an additional FYI: The Esc key automatically becomes enabled once
program execution stops.