Nick's guide to Excel / VBA Interview Questions

Nick's picture

I have hosted hundreds of Excel / VBA interviews, and I can work out very quickly how good someone is.

The biggest blunder to make in an interview is to say something like: "Oh, that's easy, I can do that in 5 minutes with the compiler and help files"

Well, in my interview, you have no compiler and no help files, so don't ignore syntax, or rely too much on help files.

You wouldn't turn up to a French interview with a dictionary and translate every word now would you ?

; - >

Here are some Excel VBA interview questions I might ask you.. Add a comment if you think you have a good answer.

Diagonal

My mistake,  I read it as a continuous diagonal.  So we start from the other end.

Option Explicit
Sub Diagonal()
    Dim i As Long
    With ActiveSheet.UsedRange
        i = Application.Min(.Rows.Count, .Columns.Count) + 1
    End With
    Do
        i = i - 1
    Loop Until Range("A1").Offset(i - 1, i - 1) <> ""
    Cells(1, 1).Resize(i, i).Select
End Sub
 

Nick's picture

Diagonal

closer, but still not bullet proof..

Diagonal

Sub Diagonal()

Dim iLast As Integer
iLast = 1
Do While Trim(Cells(iLast, iLast)) <> ""
iLast = iLast + 1
Loop
iLast = iLast - 1
Range(Cells(1, 1), Cells(iLast, iLast)).Select

End Sub

Nick's picture

Diagonal

this won't always work

Could you please give an

Could you please give an example when this won't work ? (this is assuming A1 always in not empty and "space" is not being an "entry"). May be I didn't understand the requirement correctly.

Nick's picture

diagonal

That'll give it away..

; - >

There are 2 cases that will break your code.

Maybe this is better: Public

Maybe this is better:

Public Sub Diagonal()
Dim lCnt As Long

lCnt = Sheet1.UsedRange.Columns.Count
Do While IsEmpty(Sheet1.Cells(lCnt, lCnt))
lCnt = lCnt - 1
Loop

Sheet1.Range("A1", Sheet1.Cells(lCnt, lCnt)).Select
End Sub

Nick's picture

2. Calculation

Name as many ways as you can to make Excel calculate something.

Select a formula in the

Select a formula in the formula bar and press F9. this will calculate the formula but will not print it out in the cell

 Change value with Scroll bar

 Change value with Scroll bar or Spin button