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.