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.

Making a stop

Option Explicit

Sub SigFigs()
Dim num As Single 'This is an input, the number we want to reformat
Dim sigs As Integer 'This is an input, the desired number of sig. figs.
Dim numround As Double 'This is the answer

Dim exponent As Integer

num = InputBox("Enter a number:")
sigs = InputBox("Enter the number of significant figures you wish to display:")
If sigs < 0 Then
MsgBox ("ERROR")

ElseIf num = 0 Then ' We don't have to do anything with zero. Zero is just zero.
numround = 0
Else
' Below helps us determine if we're going to have to round to the
' right or two the left of the decimal point. Don't sweat out understanding
' this expression if it confuses you.
exponent = 1 + Int(Log(Abs(num)) / Log(10))

' Below makes uses the Excel function to do the rounding.
' Using Excel functions in VBA is something we will
' talk about later in more detail.
numround = WorksheetFunction.Round(num, sigs - exponent)

End If

MsgBox (numround)

End Sub
This my program for significant figures. I need to create a line by where I put my first if statement to make the program not run if I put in a value for the number of significant figures as a negative value. What should I do?

rmoulton's picture

Reply to question

Replace the:

msgbox ("ERROR") ' parentheses actually not needed here

with

exit sub

and change

elseif ' on the next line to

if

The Answer

Put an if construct after you have assigned the value to num. Check the value for num for two things.
Is it numeric? Check by using the IsNumeric function.
Is it <0
In either of the above situation use the Exit Sub statement to come out of the procedure.
You may wish to display an error message before quitting.

Vishesh's picture

Chk this out... Sub

Chk this out...

Sub SigFigs()
Dim num As Single 'This is an input, the number we want to reformat
Dim sigs As Integer 'This is an input, the desired number of sig. figs.
Dim numround As Double 'This is the answer

Dim exponent As Integer

num = InputBox("Enter a number:")
sigs = InputBox("Enter the number of significant figures you wish to display:")

If sigs < 0 Then
MsgBox ("ERROR")
Exit Sub 'New Addition to the code
ElseIf num = 0 Then ' We don't have to do anything with zero. Zero is just zero.
numround = 0
Else
' Below helps us determine if we're going to have to round to the
' right or two the left of the decimal point. Don't sweat out understanding
' this expression if it confuses you.
exponent = 1 + Int(Log(Abs(num)) / Log(10))

' Below makes uses the Excel function to do the rounding.
' Using Excel functions in VBA is something we will
' talk about later in more detail.
numround = WorksheetFunction.Round(num, sigs - exponent)
End If
MsgBox (numround)
End Sub

Ribbon

How does one limit UI with the Excel 2007 Ribbon? Is it possible to keep a spreadsheet in full-screen mode all the time?

Vishesh's picture

Show/Hide Excel 2007 Ribbon

Sub HideRibbon()

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

End Sub

 

Sub ShowRibbon()

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

End Sub

Not working in Excel 2007

I tried entering this code at the end of my code both with and without the Sub ShowRibbon() part saving and reopenig it and the ribbon was still there. Below is the code I am using; where should I enter it and is there any modifications I need to make to get it to work?

Option Explicit
'custom code by:
'Jerry Latham, Microsoft MVP, Excel group 2005-2011
'email: HelpFrom@JLathamSite.com
'
Dim savingNowFlag As Boolean

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'this code implemented to set PreviousValue to the
'active cell on a non-Audit Trail sheet when that sheet is selected
If Sh.Name <> auditSheetName Then
PreviousValue = ActiveCell.Value
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
PreviousValue = Target.Cells(1, 1).Value
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Sh refers to the worksheet that the change took place on
'Target works just like it does in Worksheet_Change() event processing
'it is just specific to the Sh sheet that the change took place on
'
Dim NR As Long
'all of the following deal with monitoring the "Reviewed by..." entries.
Dim auditWS As Worksheet
Dim currentCol As Long
Dim currentRow As Long
Dim sectionFirstRow As Long
Dim examineRange As Range
Dim anyERangeEntry As Range
Dim tempCell As Range

'if the change took place on the Audit Trail worksheet, ignore!!
If Sh.Name = auditSheetName Then
Exit Sub
End If

If Intersect(Target, Sh.Range(watchRange)) Is Nothing Then Exit Sub
'
'this section determines if the change is being made to one of the
' "Interval Reviewed By (Initials)" rows on the sheet
' by using the phrase, we don't have to keep up with rows
' just be sure to use the phrase where needed
'
'while this looks like a lot of code, it is only executed when
'you attempt to make a change to the 'Reviewed by" entries.
If InStr(UCase(Sh.Range("A" & Target.Row)), UCase(reviewPhrase)) > 0 Then
'this IS one of the special rows, determine if the
'make sure that the current user is not the same user
'that made entries into this column previously
'if they are the same, we will erase the entry and
'refuse to accept it or record it.
currentCol = Target.Column
currentRow = Target.Row
'find where this section of entries begins based on finding the
' sectionStartPhrase (STABILITY STUDY:) entry immediately above
' this Reviewed by entry
'at this time they are 24 rows apart, but it could change
sectionFirstRow = currentRow - 1
With Sh
Do Until InStr(.Range("A" & sectionFirstRow), sectionStartPhrase) > 0
sectionFirstRow = sectionFirstRow - 1
Loop
End With
Set auditWS = ThisWorkbook.Worksheets(auditSheetName)
'set a reference to the list of worksheet names on the Audit Trail sheet
Set examineRange = auditWS. _
Range(awsWSNameCol & firstAuditEntryRow & ":" & _
auditWS.Range(awsWSNameCol & Rows.Count).End(xlUp).Address)
For Each anyERangeEntry In examineRange
'is the entry regarding this Sh?
If anyERangeEntry = Sh.Name Then
'yes, test if recorded range is in same column and within the proper section
Set tempCell = Range(auditWS.Range(awsCellCol & anyERangeEntry.Row).Value)
If tempCell.Column = currentCol And tempCell.Row > sectionFirstRow And _
tempCell.Row < currentRow Then
'entry is about the same sheet and same column, within the correct section
'test the user entry for name match/mismatch
If auditWS.Range(awsUserCol & anyERangeEntry.Row) = Environ("username") Then
'the names are the same!
'reject the attempted entry
'you could throw up this message box if you want
MsgBox "You may not Review entries that you made.", vbOKOnly, "Invalid Reviewer"
Application.EnableEvents = False
If Target.MergeCells Then
Target.MergeArea.ClearContents
Target.Cells(1, 1).Select
Else
Target.ClearContents
Target.Select
End If
'remain in the cell we just cleared
Target.Select
Application.EnableEvents = True
'some housecleaning before exiting
Set anyERangeEntry = Nothing
Set examineRange = Nothing
Set auditWS = Nothing
Set tempCell = Nothing
Exit Sub
Else
'different names, jump out of the test loop
'some housecleaning before continuing
Set anyERangeEntry = Nothing
Set examineRange = Nothing
Set auditWS = Nothing
Set tempCell = Nothing
Exit For
End If
End If
End If
Next
End If

'continue on to record the entry

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

With Sheets(auditSheetName)
.Unprotect Password:=auditWSPassword
NR = .Range(awsCellCol & Rows.Count).End(xlUp).Row + 1
.Range(awsCellCol & NR).Value = Target.Address(False, False)
.Range(awsWSNameCol & NR).Value = Sh.Name
.Range(awsDateCol & NR).Value = Now
.Range(awsUserCol & NR).Value = Environ("username")
.Range(awsPrevValCol & NR).Value = PreviousValue
.Range(awsNewValCol & NR).Value = Target.Value
.Protect Password:=auditWSPassword
End With

If PreviousValue = "" Then
With Sheets(auditSheetName)
.Unprotect Password:=auditWSPassword
.Range(awsReasonCol & NR).Value = "New Data"
.Protect Password:=auditWSPassword
End With
Else
'get reason for change from user via UserForm
UserForm1.Show
End If
Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim auditWS As Worksheet
Dim CellListRange As Range
Dim anyCellListed As Range
Dim sName As String
Dim cAddress As String

If savingNowFlag Then
savingNowFlag = False ' reset
Exit Sub ' no re-entry while doing it already
End If

Set auditWS = ThisWorkbook.Worksheets(auditSheetName)
If auditWS.Range(awsCellCol & Rows.Count).End(xlUp).Row < firstAuditEntryRow Then
Set auditWS = Nothing
Exit Sub ' no entries on the Audit Trail sheet other than the headers.
End If

Set CellListRange = auditWS.Range(awsCellCol & firstAuditEntryRow & _
":" & auditWS.Range(awsCellCol & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False
For Each anyCellListed In CellListRange
If IsEmpty(auditWS.Range(awsLockedCol & anyCellListed.Row)) Then
'need to lock the cell on the specified worksheet
'Exception to the rule:
' If reason for change was "Typographical Error" AND the
' cell in question is still empty, then don't lock it
' and change the error-maker's name so that it won't
' trigger as "cannot audit your own entries" later.
'
sName = auditWS.Range(awsWSNameCol & anyCellListed.Row)
cAddress = auditWS.Range(awsCellCol & anyCellListed.Row)
If IsEmpty(Worksheets(sName).Range(cAddress)) And _
auditWS.Range(awsReasonCol & anyCellListed.Row) = "Typographical Error" Then
' change the user id to have a "-" in front of it if it doesn't already
' so that username doesn't match for "can this user audit this data" later.
sName = auditWS.Range(awsUserCol & anyCellListed.Row)
If Left(sName, 1) <> "-" Then
auditWS.Unprotect Password:=auditWSPassword
auditWS.Range(awsUserCol & anyCellListed.Row) = _
"-" & auditWS.Range(awsUserCol & anyCellListed.Row)
auditWS.Protect Password:=auditWSPassword
End If
Else ' do need to lock it and mark it as locked
With Worksheets(auditWS.Range(awsWSNameCol & anyCellListed.Row).Value)
.Unprotect Password:=nonAuditWSPassword
If .Range(anyCellListed).MergeCells = True Then
.Range(anyCellListed).MergeArea.Locked = True
Else
.Range(anyCellListed.Value).Locked = True
End If
.Protect Password:=nonAuditWSPassword
End With
'record the locked status on the Audit Trail sheet
With auditWS
.Unprotect Password:=auditWSPassword
.Range(awsLockedCol & anyCellListed.Row) = "Locked"
.Protect Password:=auditWSPassword
End With
End If
End If
Next

Application.DisplayAlerts = False
savingNowFlag = True
ThisWorkbook.Save
savingNowFlag = False
Application.DisplayAlerts = True

'housekeeping - release objects back to the system for reuse
Set CellListRange = Nothing
Set anyCellListed = Nothing
Set auditWS = Nothing
End Sub

Private Sub Workbook_Activate()
'the _Activate event is triggered when:
' you open the workbook, or
' you switch back to this workbook after
' using another workbook that is also open.
'
With Application.CommandBars("Cell")
.Controls("Cut").Visible = False
.Controls("Copy").Visible = False
.Controls("Paste").Visible = False
.Controls("Paste Special...").Visible = False
.Controls("Insert...").Visible = False
.Controls("Delete...").Visible = False
.Controls("Clear Contents").Visible = False
.Controls("Filter").Visible = False
.Controls("Sort").Visible = False
.Controls("Insert Comment").Visible = False
.Controls("Format Cells...").Visible = False
.Controls("Pick from drop-down list...").Visible = False
.Controls("Name a Range...").Visible = False
.Controls("Hyperlink...").Visible = False
' .Controls("Look up...").Visible = False
End With
End Sub

Private Sub Workbook_Deactivate()
'the _Deactivate event is triggered when:
' you close the workbook, or
' you switch to use another workbook that is also open.
'
With Application.CommandBars("Cell")
.Controls("Cut").Visible = True
.Controls("Copy").Visible = True
.Controls("Paste").Visible = True
.Controls("Paste Special...").Visible = True
.Controls("Insert...").Visible = True
.Controls("Delete...").Visible = True
.Controls("Clear Contents").Visible = True
.Controls("Filter").Visible = True
.Controls("Sort").Visible = True
.Controls("Insert Comment").Visible = True
.Controls("Format Cells...").Visible = True
.Controls("Pick from drop-down list...").Visible = True
.Controls("Name a Range...").Visible = True
.Controls("Hyperlink...").Visible = True
' .Controls("Look up...").Visible = False
End With
End Sub

Vishesh's picture

Copy the ShowRibbon() &

Copy the ShowRibbon() & HideRibbon() procedures in a general module and call them from any where in your code. In your code above I don't see these two functions to show/hide the ribbon.

You can try these functions in a new workbook. Copy Paste the following code in a workbook module and save it as a .xlsm file. When you open the file the ribbon is hidden and while you close the file ribbon is back.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",true)"
End Sub

Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",false)"
End Sub

Thank you very much!!! I

Thank you very much!!! I greatly appreciate your help!!!!

Hakim's picture

10. Missing and repeated number

You have a set of consecutive numbers from 1 to N, we remove a number at random and duplicate another number from the remaining, we place these numbers in an array and shuffle it. Write a VBA function when passed that unsorted array prints the missing and the duplicated numbers.

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

Hakim's picture

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

Nick's picture

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

Nick's picture

8. Volatile functions part 2

Name as many volatile functions as you can

Volatile functions part 2

OFFSET
INDIRECT
VLOOKUP
HLOOKUP

They are

RAND
RANDBETWEEN
TODAY
NOW
ROW
COLUMN
 
 
Nick's picture

7. Volatile functions

What's a Volatile function ?

Functions that will trigger

Functions that will trigger recalculations every time a calculation is performed, e.g. F9.

Nick's picture

6. VLOOKUP vs INDEX MATCH

What's the difference between using VLOOKUP vs INDEX + MATCH ?

difference between using VLOOKUP vs INDEX + MATCH

Vlookup have a limitation of searching, that it only search in right side, of the searching data,

 

Example

Emp table

Emp_Code   Emp_name   Emp_ID

011                abc              121

012                adef           131

Result

Emp_Name     Emp_Code

abc                vlookup(not able to do that search)

adef               index&match function can do that type of searching

 

 

VLOOKUP vs INDEX MATCH

VLOOKUP works correct only with sorted values... and can lookup in the 1st column only..
Index + Match is best for getting values from any part of the array....

Vlookup must always return a

Vlookup must always return a value to the right of the lookup column. Using the Index and Match functions together, you can return a value to the left of the lookup column.

Vlookup will allow you to

Vlookup will allow you to search for an item in the 1st column.
The other will allow the seach param on any column

Nick's picture

5. Excel Function

You have the following data: Data

Write a function to go in cell C3 that will return the house name that corresponds to the number typed in C2

Ans 5

=IF(B2="","", VLOOKUP(B2,E2:F8,2,0))

Solution

No need of using if function for this.

Formula is =VLOOKUP(C2,E2:H8,2,FALSE)

Thats it, it returs the value searching for is Building name

Nick's picture

Ans 5

Nop

Ans 5

why ?

Nick's picture

Ans 5

there's a small oversight in the references

VLOOKUP(C2,E2:F8,2,0)

VLOOKUP(C2,E2:F8,2,0)

It would say this was wrong...

i think its
VLOOKUP(C2,E3:F8,2,false)

that is E3 instead of E2

Nick's picture

4. Excel function

Write an Excel function that calculates the first day of the current month

The first day of the current month right now is: 01-Jan-2009

Ans: 4. Excel function

=TEXT(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)),"DDDD")
or

=TEXT(WEEKDAY(DATE(YEAR(Today()),MONTH(Today()),1)),"DDDD")

or

=TEXT(WEEKDAY((MONTH(Today())&"/1/"&YEAR(today()))*1),"DDDD")

or

=TEXT(WEEKDAY(DATE(YEAR(a1),MONTH(a1),1)),"DDDD")
or

=TEXT(WEEKDAY((MONTH(A1)&"/1/"&YEAR(A1))*1),"DDDD")

 

-Boss  (a)  Bhaskar-

 

Ans 4 or may be this

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-DAY(TODAY()-1),"dddd")

=TEXT(WEEKDAY(A1),"dddd

=TEXT(WEEKDAY(A1),"dddd").
Enter your date in cell A1.

=TEXT (MONTH (TEXT (TODAY(),"MM"

=TEXT(MONTH(TEXT(TODAY(),"MM")),"DDDD")

first day of the current month

dt = Year(Now()) & "/" & Month(Now()) & "/" & "1"

An even tougher question is last day of current month :)

 

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

 

ANSWER-First Day of current month

=TEXT(MONTH(NOW),"DDDD") Thanks

First Day of Current month

Sorry, your answer is wrong. I guess it will work.

=Text(Month(Now()),"DDDD")

Correct One: First day of Current month...

=TEXT(WEEKDAY(TODAY()-DAY(TODAY()-1)),"dddd") for more help, just mail me urs problem.. Thanks

find currect day of any date

=text(date(year(today),month(today),1),"dddd")

1st day better command

= DateSerial(Year(Date), Month(Date), 1)

Nick's picture

DateSerial

DateSerial is a VBA function.. not Excel

First Day of current date

=TEXT(TODAY()-DAY(TODAY())+1,"ddd, mmm dd, yyyy")

First Day

=TEXT(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)),"dddd")