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
- 137388 reads
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?
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.
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?
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
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!!!!
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.