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
- 137505 reads
Define a public constant
Define a public constant -
Const N As Integer = 20
Add two buttons on the WorkSheet -
*-Jumble - Associate following code to it -
Public Sub Jumble()
For Each myCell In Worksheets("MyCustomSheet").Range("MyList")
Do
i = CInt(Rnd * N)
Loop While Cells(i + 2, 5) <> "" And i < N + 1
Cells(i + 2, 5) = myCell.Value
Next
End Sub
*-Find Issues - Associate following code to it -
Public Sub Findissues()
For i = 1 To N
flag = 1
For Each myCell In Worksheets("MyCustomSheet").Range("RandomList")
If myCell.Value = i And flag = 0 Then
flag = 2
End If
If myCell.Value = i And flag <> 2 Then
flag = 0
End If
Next
If flag = 1 Then
Cells(i, 10) = "Missing - " & i
End If
If flag = 2 Then
Cells(i, 10) = "Repeated - " & i
End If
Next
End Sub
Now in the Sheet itself define 1 to N numbers in consecutive cells, and define this as a named range - "MyList"
Now in 2 columns away.. define the entire column as a named range -"RandomList"
Now when you press the button Jumble you will get the list of 1st N numbers jumbled up in Named range "RandomList"
Now click on the Find issues.... it will populate the results in 10th column,... as to which elements are missing and which are repeated.
Finding the missing number
Whats the use of sum of squares of the number. We just need sum of the first n numbers i.e: n*(n+1)/2
Solution:-
Function miss(n) 'n is the total number
Dim sum As Integer
Dim sum1 As Integer
Dim num As Integer
num = 0
i = 1
sum = n * (n + 1) / 2 ' sum of first n integers
sum1 = 0
While i <= n
sum1 = sum1 + Sheet2.Cells(i, 1) ' sum of numbers given in the array
If (WorksheetFunction.CountIf(Sheet2.Range("A:A"), Sheet2.Cells(i, 1)) > 1) Then
num = Sheet2.Cells(i, 1) 'check which number is repeating and save it in num
End If
i = i + 1
Wend
diff = sum1 - sum
miss = num - diff
' the missing number will be the difference between the repeating number
' and in the difference of sum of two arrays(original and modified)
End Function
Here is a hint: the sum of
Here is a hint: the sum of squares of numbers from 1 to N is N*(N+1)*(2*N+1)/6
Function miss(n) 'n is the
Function miss(n) 'n is the total number
Dim sum As Integer
Dim sum1 As Integer
Dim num As Integer
num = 0
i = 1
sum = n * (n + 1) / 2 ' sum of first n integers
sum1 = 0
While i <= n
sum1 = sum1 + Sheet2.Cells(i, 1) ' sum of numbers given in the array
If (WorksheetFunction.CountIf(Sheet2.Range("A:A"), Sheet2.Cells(i, 1)) > 1) Then
num = Sheet2.Cells(i, 1) 'check which number is repeating and save it in num
End If
i = i + 1
Wend
diff = sum1 - sum
miss = num - diff
' the missing number will be the difference between the repeating number
' and in the difference of sum of two arrays(original and modified)
End Function
Sub FindInArray(InputArray()
Sub FindInArray(InputArray() As Integer)
Dim N As Long
Dim lNSum As Long
Dim lNSqSum As Long
Dim lSum As Long
Dim lSqSum As Long
Dim lSumDiff As Long
Dim lSqSumDiff As Long
Dim iLess As Integer
Dim iMore As Integer
Dim strResult As String
N = WorksheetFunction.Count(InputArray())
lNSum = N * (N + 1) / 2
lNSqSum = N * (N + 1) * (2 * N + 1) / 6
lSum = WorksheetFunction.Sum(InputArray())
lSqSum = WorksheetFunction.SumSq(InputArray())
lSumDiff = lSum - lNSum
lSqSumDiff = lSqSum - lNSqSum
iLess = (lSqSumDiff - lSumDiff ^ 2) / (2 * lSumDiff)
iMore = (lSqSumDiff + lSumDiff ^ 2) / (2 * lSumDiff)
strResult = "Less Value = " & iLess & vbCrLf & "More Value = " & iMore
MsgBox strResult
Debug.Print strResult
End Sub
9. Pivot Tables
Explain what a pivot table does
Pivot Tables enables us to
Pivot Tables enables us to summarize & analyze data in Lists & Tables. They can automatically sort, count & total the data stored in one table or spreedsheet & creates a second table displaying the summarized data.
The user sets up & changes the structure by dragging a dropping fields graphically.
Saurabh Singh
8. Volatile functions part 2
Name as many volatile functions as you can
Volatile functions part 2
OFFSET
INDIRECT
VLOOKUP
HLOOKUP
They are