headdesk

Signing your own macros with SelfCert.exe

If you want to run your own macros without lowering the default macro security settings, you'll need to sign them with a digital certificate.

Signing your own macros with SelfCert.exe is a great description how to create your own certificate and sign your macros with it. And everything you'll need for this already comes with Microsoft Office.
headdesk

Excel Menu Key

Apparently, I've never before tried to enter a slash in an Excel sheet until now.
Because the result surprised me - if you type /, it doesn't enter the character in the cell, but it activates the keyboard access to the Excel menu. Which is really annoying if you actually want to enter / in a cell.

Fortunately, this behavior can be turned off and this is how:

Excel 2007
1. From the Office Button menu, press the button Excel Options.
2. Choose Advanced, and then scroll down until you see the Lotus compatibility section.
3. In the field Microsoft Office Excel menu key, replace the slash with a character that you do not use frequently or delete it, and then click OK.

Excel 2003
1. From the Tools menu, select Options
2. Select the tab Transition
3. In the field Microsoft Office Excel menu key, replace the slash with a character that you do not use frequently or delete it, and then click OK.

And to make this a post about VBA, you can switch off this property in VBA as follows:
Application.TransitionMenuKey = ""
headdesk

Array Formulas / Shortcuts in Excel

Long time no post...
And this post has nothing to do with VBA either, but I'd like to point out two incredibly useful Excel tutorials by [info - personal] slowfox

1. Have you ever heard of Array Formulas in Excel? No? Or you did, but never really understood how they work? Then have a look at this tutorial. Although the described tasks would also be solvable with ordinary formulas, they're a great example to understand the concept of array formulas. And as soon as your task is something else than counting or adding up or gets more complex, array formulas are a powerful tool that might save you from coding macros.

2. Working with a program is always quicker and more efficient if you don't have to use keyboard and mouse in parallel. You can hardly work with Excel using only the mouse, but you can try to do everything with your keyboard to get faster. Here's a nice overview of Excel shortcuts that will free you from your mouse maybe not entirely, but a big lot.

headdesk

Checking if an Object Exists in a Collection

Excel uses collections of objects a lot, and sooner or later you'll be in the need of checking if a certain object exists in a collection. For example, to check if a workbook was already opened, check if it's in the collection of open workbooks (Application.Workbooks), to check if a certain worksheet exists, check if it's in the collection of worksheets (e.g. ActiveWorkbook.Worksheets) and so on...

Collapse )

And this approach works for all collections of objects that Excel uses, not only workbooks and worksheets.

headdesk

Individual Buttons in Worksheets and Documents

You want individual buttons? Using the formatting options for CommandButtons will get you only so far. You can change the background color, the font, and you can even put a picture on the button, but the button will always be rectangular.

So if you want round buttons (or any other form) on your sheet, use shapes. You can draw them in whichever way you like. You can make them look as complex and colorful as you like by drawing several shapes and grouping them together.

Collapse )
Collapse )
Collapse )

headdesk

Search Cells and Highlight Found Entries

Imagine you have a long list in your excel sheet and want to find certain entries. Especially if the list gets bigger, it'd be nice to just tell Excel what you're looking for, and have Excel automatically find it. It's faster and more accurately than searching yourself.

Collapse )

The key word is Conditional Formatting. You can define a conditional format for the list that shows only when the cell value matches the search value. This method has the advantage that you don't have to worry about the original cell formats at all.

Collapse )
Collapse )
Collapse )

Ok, so this post didn't contain VBA code, but only because it was easier to do this without...