Daily posts of Excel tips…and other stuff
Archive for the ‘VBA Functions and Keywords’ Category.
I have a table of numbers and formulas for each row, column, and for the table as a whole. The table is part of a report - the output of the application. The user wants to exclude certain numbers from the calculations after reviewing them. These numbers would be outliers and would [...]
Hi All,
If you do some VBA programming regularly, you'll be acquainted with the Object browser, which neatly shows you all objects, properties and methods belonging to any object in Excel (or any other library you have referenced in your VBA project).
Whilst this is a tremendous tool to look for information on just a couple of [...]
I often use Excel's macro recorder to perform repetitive tasks.
It's usually a list of cells with a handful of exceptions. That is, I cant just run the macro from start to finish - I have to give each item a brief glance before the macro runs.
At the end of the macro, it's handy to position [...]
JWalk wrote about a Keyless Entry Hack. "That shouldn't be too hard to duplicate", I thought. I get 3,133, but I have four duplicate numbers. Here's part of my immediate window.
How do I get rid of those duplicates?
Sub MakeKeyCombo()
Dim i As Long, j As Long, k As Long, [...]
As you may recall, I've written about registering User Defined Functions before.
The problem with the trick I used in that post, was (and this isn't trivial): it crashed Excel.
KeepITCool came up with yet another trick to make this work, which I have described in
this article.
Enjoy!
Regards,
Jan Karel Pieterse
JKP Application Development Services
One of my new year's resolutions was to write a procedure to ftp picture files to this blog to simplify the procedure. It took two days, but I finally got it done. I don't know if it's well done, but it works. I got a little help along the way, specifically:
Forestasia has [...]
I've been thinking about bits and two's complement lately. Really fun stuff. When I was messing with alternating columns earlier today, I came upon this in the Immediate Window:
?not 2
-3
What the heck? Eventually, I looked up the Not Operator in help and read this
In addition, the Not operator inverts the bit values [...]
I needed to alternate between columns A and B when writing to a bunch of rows in VBA. I created a variable, lStartCol, that I intended to alternate between zero and one and use in an Offset property.
lStartCol = rCell.Row Mod 2
rCell.Offset(lStartCol, 0).Value = "Some number"
I thought that was pretty clever, but I discovered [...]
