Home > Advanced, Beginner, Excel, Excel VBA Resources, Intermediate, Interview, VBA > Excel VBA Interview Questions (aka Things every *good* Excel VBA developer should know)

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?

  • What are named ranges?  How do you create one , and why are they useful?
  • What does the ‘ character do?  Why is this useful
  • Explain Conditional Formatting
  • Explain how to restrict the values in a cell to only days of the week.

Medium

  • How can you see what an element of an Excel formula resolves to.  i.e.
=AND(ABS(D61-AB61) >= VLOOKUP($B$59, $I$36:$L$39, 2, FALSE),ABS(D61-AB61) <
VLOOKUP($B$59, $I$36:$L$39, 3, FALSE))

You want to know what the highlighted section below will resolve to as there is an error somewhere in the formula ( there are 2 ways to do this)

=AND(ABS(D61-AB61) >= VLOOKUP($B$59, $I$36:$L$39, 2, FALSE),ABS(D61-AB61) <
VLOOKUP($B$59, $I$36:$L$39, 3, FALSE))
  • What is the short cut to enter an Array formula?
  • What is the short cut for the Format Cells Dialog?
  • What are the different ways to make a sheet calculate, and how do you make this happen?
  • Without looking at any documentation,  or at Excel give the parameters to VLOOKUP
  • What does Volatile mean, giving examples of the most common volatile functions
  • How can you get the nth biggest number from an Range?  Nth smallest ?  ( No VBA )
  • What is the shortcut to SUM all of the above cells?  There is also a one click method to do this too.   Bonus point to describe the different behaviour that can be seen when trying this

Hard


  • What are the limitations surrounding VLOOKUP and what can you do instead to get round these?  ( Alternative Excel functions )
  • Create an Excel formula only solution to find the 2nd and 3rd values that match a VLOOKUP call rather than just the first
  • Explain the excel dependency Tree

Challenge

  • Create a spreadsheet that will work out the tax you should pay based on the PAYE UK tax system.  ( For non UK people you might have to look some of this up).   So 0% to 6448, 22% to £43k and 40% to £150,000  and 50% above that ( BUT with the entitlements taken away!)
  • Create a chessboard pattern in Excel.

VBA Interview Questions

Easy

  • What does Option Explicit do?
  • Explain the Excel Object Model
  • What is a Variant, what the pros and cons of its use?

Medium

  • Name the other Option commands and what they do
  • What is a UDF?
  • How do you make your UDF Volatile?
  • Is VBA Object Orientated?  What features of the language support your answer either way?
  • What is wrong with the statement below, and how can it be fixed
    • A, B, C As Integer

Hard

  • Write an algorithm which will inform the user of the dimensions of any array passed in.

Wow

  • Return the dimensions of the array as in the Hard section, but without using any error handling.  There are 2 ways this is possible.

Excel Power-User Questions

I have to admit, I will have to do another section on Power User stuff, so this will be brief.  It might well get moved out completely.  I consider power user stuff to be Charting, Pivot tables, Macro Recording.  Stuff where you are doing one task and making your life easy.  I think the cross into Excel developer means you actually build something you can give to someone else, where you have some requirements and need to develop something a little controlled that solves a business problem which is repeatable and non trivial.  This is where the solution goes beyond a recorded macro.  Power user solutions usually involve tweaking a macro, dumping data into a sheet and then adjusting a Pivot table etc.

Easy

You should be able to identify what these Toolbars are, and why you would need to use them.

Toolbar 1

toolbar)_1

Toolbar 2

toolbar 2

Toolbar 3

toolbar 3

Hard

  • I can tell you that Toolbar 2 and Toolbar 3 are distinct.  They have some of the same icons, however they are NOT the same in this case.  Why?
  • What makes them different?  There is one common example on how using the icon pictured will mean you have to do different things.. Explain.

Shortcuts

Knowing good shortcuts is indicative of an high level Excel Power user, and often a developer that is highly involved in Excel VBA on a daily basis.

  • Ctrl+[
  • Ctrl+]
  • F5
  • F2

Link here : to some of the most comprehensive lists :

Advertisements
  1. FinancialRadDeveloper
    June 24, 2010 at 9:40 am

    Please feel free to leave comments on what answers you have come up with. In a few days I will add a new post with all the answers in.

  2. Del
    August 25, 2010 at 2:23 pm

    Well I have only been doing VBA stuff with Excel for the last 10 years and consider myself an expert. However I failed your first test because I had no clue about alt F11, and also no idea what ctl D does.
    Years ago I decided to ignore all keystroke shortcuts (except ctrl CXZV) because if you take MS Office as a whole there are HUNDREDS of them and I did not want to memorise them.
    This morning I noticed that an Excel workbook I saved yesterday had an extra sheet in it called Macro1. What is alt F11 for ? It is to irritate you when you hit ctrl F11 instead. What are keyboard shortcuts for ? They are there to enable you to carry on when your mouse does not work.
    THAT is the right answer.

  3. FinancialRadDeveloper
    October 19, 2010 at 4:14 pm

    I agree with some of the points you have made. However, I was mainly talking about something that could be used to check whether the person in the interview has been a super user / Excel VBA developer for years.

    There will be many other ways and I think if someone were to show great competance in other areas I wouldn’t care if they had this esoteric knowledge? 🙂

  4. Duncan
    May 13, 2011 at 8:26 am

    I’m enjoying this, but where’s the separate post with the answers?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: