Nick's guide to Excel / VBA Interview Questions
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.
»
- Nick's blog
- Login or register to post comments
- 136084 reads
What about this one: Sub
What about this one:
Sub GetDiagonal()
Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
With ActiveSheet.UsedRange
lRowCt = .Rows.Count
lColCt = .Columns.Count
For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1
If .Cells(lCt, lCt) <> "" Then
ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Cells(lCt, lCt)).Select
Exit For
End If
Next
End With
End Sub
select diagonal
close, but it won't always work.. there's a small bug too, you'll kick yourself when you see it.
I see a couple of potential
I see a couple of potential problems with my previous entry:
- The used range has more rows than there are columns available
- The active sheet isn't a worksheet
To cater for this, I modified the code like this:
Sub GetDiagonal()
Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
If TypeName(ActiveSheet) = "Worksheet" Then
With ActiveSheet
lRowCt = Application.Min(.UsedRange.Rows.Count, .Columns.Count)
lColCt = .UsedRange.Columns.Count
For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1
If .Cells(lCt, lCt) <> "" Then
.Range(.Range("A1"), .Cells(lCt, lCt)).Select
Exit For
End If
Next
End With
End If
End Sub
Another problem may be that a
Another problem may be that a cell evaluates to an error result, to avoid a runtime error the cod emust be modified further to:
Sub GetDiagonal()
Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
If TypeName(ActiveSheet) = "Worksheet" Then
With ActiveSheet
lRowCt = Application.Min(.UsedRange.Rows.Count, .Columns.Count)
lColCt = .UsedRange.Columns.Count
For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1
If .Cells(lCt, lCt).Text <> "" Then
.Range(.Range("A1"), .Cells(lCt, lCt)).Select
Exit For
End If
Next
End With
End If
End Sub
hi guys, thanks a lot for
hi guys, thanks a lot for this brain teasing assignement.
Nick, please can you have a look at my code fully based on the previous one.
i change MAX by MIN in this line:
For lCt = Application.Max(lRowCt, lColCt) To 1 Step -1
doing so i think that it might increase the execution time as we dont need to evaluate unnecessary cells.
Sub GetDiagonalBonero()
'code fully on previous.
Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
If TypeName(ActiveSheet) = "Worksheet" Then
With ActiveSheet
lRowCt = Application.Min(.UsedRange.Rows.Count, .Columns.Count)
lColCt = .UsedRange.Columns.Count
For lCt = Application.Min(lRowCt, lColCt) To 1 Step -1
If .Cells(lCt, lCt).Text <> "" Then
.Range(.Range("A1"), .Cells(lCt, lCt)).Select
Exit For
End If
Next
End With
End If
End Sub
Congratulations
well done... u nailed it.
GetDiagonal
Sub GetDiagonal()
Dim lRowCt As Long
Dim lColCt As Long
Dim lCt As Long
Dim i As Integer
If TypeName(ActiveSheet) = "Worksheet" Then
With ActiveSheet
lRowCt = .UsedRange.Rows.Count
lColCt = .UsedRange.Columns.Count
lCt = Application.Max(lRowCt, lColCt)
If .Cells(lCt, lCt).Text <> "" Then
.Range(.Range("A1"), .Cells(lCt, lCt)).Select
Else
For i = 1 To lRowCt
If .Cells(i, lColCt).Text <> "" Then
.Range(.Range("A1"), .Cells(i, lColCt)).Select
End If
Next i
End If
End With
End If
End Sub
GetDiagonal simple way
Range("A1").Select
Do While ActiveCell.Offset(1, 1) <> ""
ActiveCell.Offset(1, 1).Select
Loop
Range("A1:" & ActiveCell.Address).Select
Diagonal
Option Explicit
Sub Diagonal()
Dim i As Long
Do
i = i + 1
Loop Until Range("A1").Offset(i, i) = ""
Cells(1, 1).Resize(i, i).Select
End Sub
Diagonal
Excellent start, but that won't always work