Archive

Archive for the ‘Excel VBA Resources’ Category

G Challenge III – Practice Excel VBA Interview question – Watch face maths


I have heard of this question coming up a little bit in all sorts of technical interviews so I thought I would set it as a G Challenge.

Question:

“Please tell me what you would implement to tell me the angle between then hands on a watch face at any given time of the day.  Please show your working and use these examples.”

6:00, 3:00, (lets make this non trivial now ) 9:15, 1:30 12:50,  3:20.

Answer Style:

It is up to you how you go about answering this, but I would try this in two stages. 

  1. Have a pen and paper and go about it as if you were in an interview, then :
  2. Actually try to write some VBA for a spreadsheet that goes about doing it.  Esp if you can call it from the sheet so your function returns the answer from an Excel Date Time entered in a cell.

Extra Homework:

  • Could you write this as a VB dll, C++ dll, C# XLL etc?
  • Does your function cope with 24hr time format?
  • Can you also output a list of times from an angle?  Ie. what times equate to 90 deg, 27 deg etc? 
    • If so can you output this as an array function so that a list can be shown in Excel?

Essential tools for Excel VBA RAD (Rapid Application Development)

February 17, 2011 1 comment

I guess a major point of RAD is speed.  The clue is right there in the name: Rapid Application Development.

So, all other things being equal, how do we go about getting more speed.  I don’t mean smashing out a load of garbage or not testing things. 🙂

I mean what things can be used to help productivity?  I am going to list a few here.

I will keep adding to this list, but if anyone knows of any good sources please add a comment and I will add it in.

Do you use protection? Really useful factoid – 002


We are all told that using protection is a good thing.  This applies to both real life and also programming, both in full OOP and the quasi OOP world of VBA / VB. It can be possible in some cases for your variables to be changed inside the sub / function you have called, even the ones one may have designated in your human mind as ‘input’ variables.   This can lead to odd behaviour and hard to find bugs.

So.. what am I going  on about?  Well I am talking about passing by value or by reference.  (Experienced VBA Programmers may want to skip this bit).   In VBA you can use two keywords ByRef or ByVal when defining your Sub or Function.   Read more…

G Challenge I

November 25, 2010 3 comments

Ok, my friend is trying to learn Excel VBA and get into RAD development.   I am going to set a number of challenges which I am going to call a ‘G Challenge’  😛

So the first.  Take a string in the format :

20101124_170615_dd20101124_EOD.g

where the format is :

yyyymmdd_hhmmss_ddyyyymmdd_EOD.g

In VBA I would like 2 things. 

  1. The string back in the format dd-mmm-yy hh:mm
  2. An actual date serial

Example above would go to 

  1. 24-Nov-2010 17:06:15
  2. 40506.7126736111

Go!   I would use 2 seperate functions to start off with.

Answers will be forthcoming in a post G Answer I

Essential material for Excel VBA RAD Development


I have been doing Excel VBA RAD ( Rapid Application Development) for over 4 years now, and I thought it would be useful to list all of the most useful resources that I have found over the years in one post.   I guess as much for my own benefit as for other readers.

Print :

Excel Development:

Professional Excel Development:

This has to be “The Bible” of all professional Excel developers.  I am being quite bold in this claim.  Read more…

Categories: Excel VBA Resources

Excel VBA Interview Questions (aka Things every *good* Excel VBA developer should know)


I thought that I would compile a list of things that *I* think every good Excel developer should know.   I don’t mean techniques in particular, just things that people who work with Excel VBA intensely each day should know.

Edit: I think a lot of people will be searching for Excel VBA Interview questions and answers so I have renamed the post and changed the format slightly.  I will include all the answers on a separate post so people can try these for themselves and use it to check, or just go there immediately and cheat ;-P

I’ll illustrate :   Alt+F11.  If you don’t use this, or don’t know when asked what it does, I would say you know Jack about VBA.    This is only my own opinion, but a lot of these things do come up as interview questions as they are quite esoteric and won’t really be something a pretender would, or come to think about it, could Google for when cramming for the interview.

I’ll start simple and group things up so they should all make sense.

Excel Interview Questions

Easy

  • What does Shift+D do in this case?
  • What does Ctrl+D do in this case?

  • What will happen if I pull down on the corner using the plus symbol making the 1×3 cell deep selection grow to 1×10?

Read more…

Excel VBA Interview questions


I started this blog mainly to record my experiences while looking for Excel / VBA roles.   I do remember that there were a number of blogs that I myself found very useful.  This one always stick in my mind as I remember using 2 posts on the blog for both my current spate of job hunting and also the previous spate where I first started to look for Excel / VBA / RAD roles when I worked out I just wasn’t going to make a very good C++ programmer.

Excel VBA Questions:

http://www.toomik.net/helen/blog/2007/01/31/interviewing-for-an-excel-vba-job/

Hiring:

http://www.toomik.net/helen/blog/2006/02/28/hiring/

I’ll post some more thoughts up on a re-edit, I just thought  I would post this up right now in my lunch break first.