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 :