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.

How about this solution

Private Sub DiagSelect()
Dim colCount As Long
'Get the last column populated with data
colCount = ActiveSheet.UsedRange.Columns.Count

ActiveSheet.Range("A1", ActiveSheet.Cells(ActiveSheet.Rows.CountLarge, colCount).End(xlUp).Address).Select

End Sub

another solution

Sub Diagonal()

Dim i As Integer, j As Integer

' take advantage of fact that we have only 256 columns (at least up to 2003 :))
Do While (Cells(256 - i, 256 - j) = "")
i = i + 1
j = j + 1
Loop

Range(Cells(1, 1), Cells(256 - i, 256 - j)).Select

End Sub

My solution

Sub Diagonal()
Dim Rowcount, ColumnCount As Long
Rowcount = ActiveSheet.UsedRange.Rows.Count
ColumnCount = ActiveSheet.UsedRange.Columns.Count
Dim i As Long
i = WorksheetFunction.Max(Rowcount, ColumnCount)
Do While ActiveSheet.Cells(i, i) = ""
i = i - 1
Loop
Cells(1, 1).Resize(i, i).Select
End Sub

Re: VBA Range Selection Question

Hope the below code will answer your question:

Sub GetDiagonal()
Dim lLRow As Double
Dim lLCol As Double

ActiveSheet.Cells(1, 1).Select

With ActiveSheet.UsedRange
ActiveCell.SpecialCells(xlLastCell).Select
lLCol = Selection.Column
lLRow = Selection.End(xlUp).Row
End With

ActiveSheet.Range(Cells(1, 1), Cells(lLRow, lLCol)).Select
End Sub

-Boss-
-Excel Student-

Re: VBA Range Selection Question

The Above code submited by me.

 

-boss-

-Excel Student-

how about this?

Sub selectdiagonal()
Cells(1, 1).Select
i = 1
While Cells(i, i) <> ""
i = i + 1
Wend
i = i - 1
Cells(i, i).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
End Sub

Efficient diagonal selection

With Sheets(1)
Set f = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
.Range(.Range("A1"), .Cells(f.Row, f.Column)).Select
End With

select diagonal range

Public Sub selectDiagonal()
Dim c As Range, rDiagonal As Range
Dim i As Long

Set c = ActiveSheet.[b2]

While Not c.Column = Sheet1.Columns.Count
If LenB(c.Value2) <> 0 Then Set rDiagonal = c
Set c = c.Offset(1, 1)
Wend

ActiveSheet.Range([a1], rDiagonal).Select
Set c = Nothing
Set rDiagonal = Nothing
End Sub

My first try in VBA

 Sub SelDiag()

ActiveSheet.Range("a1").Select

Do Until Selection.Value = Empty

    Selection.Offset(1, 1).Select

    Loop

DiagLen = Selection.Column - 2

ActiveSheet.Range("a1", Range("a1").Offset(DiagLen, DiagLen)).Select

End Sub

Nick's picture

Excellent 1st try

...good effort.

Have a look at the other solution posted recently..

What most people omit are the following:

  • Column limitations
  • Handling errors returned by the cell formula
  • Work on all types of sheet