jkp-ads.com Newsletter for May 2015
I've been very busy in May with a full-time development project running the entire month and teaching two courses "Excel VBA for Financials", which were very well received. Hectic but quite rewarding.
Anyway, lets do some tips and tricks.
Jumping to your favourite VBA routine from Excel
Did you know you can use the goto window to jump to a macro in your workbook? Just hit F5 and type the macro's name and hit enter.
Another neat trick is to use the Hyperlink function for the same goal. Just make sure you prepend the subroutine name with the # symbol. So if the name of your routine is in cell A1 and the description in B1:
=HYPERLINK("#" & A1,B1)
This is a very neat way to create a table of contents for a macro workbook when you're doing a presentation about VBA.
Slow response of your worksheet when editing
Someone sent me a workbook the other day, which responded extremely slow when moving the cellpointer around. It could take as much as ten seconds after hitting an arrow key, before the cursor would actually move. After some digging I found the root cause: the sheet contained hundreds of zero-sized objects floating on top of it. Probably a remnant from copy/pasting from a website table. So how do you get rid of these?
- Delete all of them: F5, Special, Objects, hit Del key
- Look at what's there first (warning, this makes the file respond even slower!): Click on the Home Tab and on the far right click the Binoculars and choose "Selection Pane...". Here you can click any object to delete it.
Amsterdam Excel Summit 2015 well received
We always do a survey after our events and I wanted to share the results of our famous Amsterdam Excel Summit 2015 with you.
I am still in the early planning stages of an "Advanced Excel VBA" course. I have put up a survey to ask opinions on course content and whether or not you'd be interested in such a course.