Questions

1) What is the Personal.xlsb ?

This is the personal macro workbook.
This is a special hidden workbook which you can use to store your macros.
It is open every time Excel is open assuming one has been created.


2) What is the largest Excel file you can have ?

2 GB of memory
Either for a single workbook or a combination of open workbooks


3) If a VBA macros was running and needed to be interrupted what keys would you press to interrupt it ?

(Ctrl + Break)
Escape


4) A macro is running very slowly, what changes could you make to speed it up ?

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
Worksheet.EnableFormatConditionsCalculation = False
Read and write large blocks of cells in a single operation
Avoid selecting or activating any objects
Declare all variables with explicit data types
Never use the Variant data type


5) What is the difference between ThisWorkbook and ActiveWorkbook ?

Application.ActiveWorkbook 
Application.ThisWorkbook

ActiveWorkbook - the workbook in the active window
ThisWorkbook - the workbook where the code is actually running


6) What is the difference between UsedRange and CurrentRegion ?

ActiveSheet.UsedRange.Row 
ActiveSheet.ActiveCell.CurrentRegion.Select

UsedRange - the cell range of all the used cells on the worksheet.
CurrentRegion - the cell range inside blank rows and columns.


7) What is the difference between Range.Value and Range.Value2 ?

??


8) Is it possible to hide a worksheet so the user cannot unhide it ?

Sheets(2).Visible = xlSheetVeryHidden 


9) Write code to display the values in cells B1 to B5 in the Immediate Window ?

Dim lrowno As Long 
For lrowno = 1 To 5
   Debug.Print Range("B" & lrowno).Value
Next lrowno


10) What is a Named Range >

??


11) What are the advantages of using Named Ranges ?

You can insert or delete cells, rows and columns and the cell references remain intact.


12) What is the difference between a Workbook Level named range and a Worksheet Level named range ?

??


13) What type of named range is created when you use the Name Box ?

??


14) What is a Dynamic Named Range ?

??


15) What are Natural Language Formulas ?

??


16) Write the code to populate an array with the values in a Named Range ?

Dim arValues As Variant 
arValues = Range(NamedRange).Value
arValues = Application.Evaluate[NamedRange].Value
arValues = [NamedRange].Value


17) Can you describe Automatic Calculation ?

Excel will perform smart recalculation on all open workbooks whenever anything changes.
When you open a workbook if anything has changed then a smart recalculation is performed.
When you open a workbook that was saved in a previous version a full calculation is performed.


18) When should you change your calculation option to Manual ?

For workbooks that take more than a couple of seconds to recalculate.
If not then there will be a delay whenever anything changes.


19) If the word Calculate appears in the status bar, what does this mean ?

Your calculation is set to Manual and a formula in the active workbook needs recalculating.
The workbook contains circular references and the iteration option is switched on.
You have set the Workbook.ForceFullCalculation property to True.
The dependency tree has reached its limit.


20) The second time you calculate a workbook it can be significantly faster than the first time, why is that ?

Excel usually only recalculates cells and dependencies that have changed.
Excel stores and reuses the most recent calculation chain which means it doesn't have to build it again.
Excel caches recently used data.


21) What happens if you are calculating a sheet and the user presses one or more keys ?

The calculation will be interrupted and will not complete.


22) A workbook is taking ages to update/recalculate, what changes could be made to speed it up ?

The speed taken to update depends on the number of cell references, the calculation operations and the efficiency of the functions being used.
Having more rows with fewer calculations will update faster than fewer rows with more complex calculations.
Remove any duplicated calculations
Remove any array formulas or replace them with user defined functions
Change the lookups to be performed on sorted data rather than unsorted
Use Excel or VBA built-in functions where possible
Reduce the worksheet usedrange
Avoid using any volatile functions
Replace dynamic named ranges with structured referencing
Design formulas to be forward referencing so they do not refer to any cells to the right or below
Avoid inter-workbook links
Consolidate data to fewer worksheets
Move all lookups and reference data to a single worksheet
Take out any references or expressions from your array formulas
Initiate the calculation from within VBA
Run time checks on different worksheets to identify bottlenecks


23) Is it possible to prevent the calculation from being interrupted ?

Yes, there are two ways this can be achieved:
If you are running from VBA using the Application OnTime.

Application.CalculationInterrupting = xlNoKey. 


24) When updating formulas what do the following shortcut keys do ?

(F9) - Smart Recalculation - recalculates all cells in all open workbooks.

Application.Calculate 


(Shift + F9) - Smart Recalculation - recalculates all cells in all the selected worksheets.

ActiveSheet.Calculate 
Worksheets().Calculate


(Ctrl + Alt + F9) - Full Calculation - calculates all cells in all open workbooks.

Application.CalculateFull 


(Shift + Ctrl + Alt + F9) - Full Calculation (dependency tree and calculation chain) - calculates all cells in all open workbooks.

Application.ForceFullCalculation (Added in 2007) 


25) What does the Range.Calculate method do ?

Smart Recalculation of the specified range.

Range("A1:D20").Calculate 


26) What does the Range.CalculateRowMajorOrder method do ?

Added in 2007.
Smart Recalculation of the specified range ignoring any forward references or within range dependencies.

Range("A1:D20").CalculateRowMajorOrder 


27) Is there a way in code to trigger a recalculation of the active sheet only ?

Togglying the EnableCalculation property and then recalculating.

ActiveSheet.EnableCalculation = False 
ActiveSheet.EnableCalculation = True
Activesheet.Calculate


28) Is there a way to check the calculation state ?

Yes. You can use the CalculateState property.

Application.CalculationState 


29) What does the Workbook.ForceFullCalculation property do and when would you use it ?

Workbook.ForceFullCalculation = True 

Returns or sets the workbook to forced calculation mode.
This property will be reset when Excel is restarted or the workbook is closed ?
When you want every calculation of the workbook to be a full calculation.
When this property is set to True the calculation time for data tables will increase significantly.
If a workbook has a large number of complex dependencies that takes ages to load or when a recalculation takes longer than a full calculation.


30) Write code that will trap the F9 events and redirect it to a subroutine.

Application.OnKey "{F9}", "HandleF9" 
Application.OnKey "+{F9}", "HandleShiftF9"

Ctrl + Shift + F9 ??
Alt + Ctrl + Shift + F9 ??


31) What are Circular References ?

They are ??
Excel calculates circular references sheet by sheet without considering dependencies.
If your circular references span multiple worksheets you can get very slow calculation.


32) Can you explain what Iterative Calculation means ?

??


33) How would you check if a workbook contains any Circular References ?

Switch off the "Enable iterative calculation" option.
Excel will provide a warning message if it encounters any circular references.


34) What is the Range.Dirty method ?

Range("A1:B10").Dirty = True 

Designates a range to be recalculated the next time a recalculation is performed.


35) Does Excel have any Multi-Threading capabilities ?

Excel 2007 introduced the concept of multi-threaded calculation (and recalculation) of workbooks
Excel tries to identify parts of the calculation chain that can be recalculated concurrently on different threads.


36) Can you give some examples of Worksheet Functions that you have used ?

??


37) Are there any built-in worksheet functions that are volatile ?

Yes, TODAY, NOW, RAND, OFFSET, INDIRECT
CELL and INFO are also volatile but it depends on their arguments.


38) When would you use the VLOOKUP function ?

??


39) What are the four arguments passed to the VLOOKUP function ?

Lookup value - the value to be found in the first column
Table_array - the cell range containing the table of data
Column_index - the column number you want returned
Exact_match - (optional) whether to find an exact match


40) What 2 functions would you use if you wanted to lookup a value that was not in the first column ?

OFFSET & MATCH
INDEX & MATCH


41) What is Application.Volatile ?

This line of code can be included in your user defined function to force them to recalculate every time a value changes on that particular worksheet.
Volatile functions will always recalculate.


42) What do the terms Conditional Summing and Conditional Counting mean ?

??


43) How would you sum all the values in column A where column B is greater than 15 ?

{=SUM(A1:A10*(B1:B10>=15))}
=SUMIF(B1:B10,">=15",A1:A10)


44) When would you use the SUMIFS function ?

??


45) When would you use the INDIRECT function ?

??


46) Write a User Defined Function to return the day of the week ?

Public Function DAYOFWEEK() As String 
   DAYOFWEEK = Application.WorksheetFunction.TEXT(Application.WorksheetFunction.NOW,"dddd")
   DAYOFWEEK = VBA.FORMAT(VBA.NOW,"dddd")
End Function


47) What is an Excel Data Table ?

Not to be confused with a regular table.
This is a type of What If Analysis that creates a table based on the impact of one (or two) variables in a formula.
They provide a way to calculate multiple variations and display and compare the results in a table layout.
There is even a calculation option called "Automatic except for data tables".


48) What is the Application.Union method ?

Application.Union 


49) What is the Application.Intersect method ?

Application.Intersect 


50) Write code to select all the non-blank cells

Application.Union(Cells.SpecialCells(xlCellTypeFormulas, 23), 
                  Cells.SpecialCells(xlCellTypeConstants, 23)).Select

The 23 = xlSpecialCellsValue.xlNumbers | xlTextValues | xlLogical | xlErrors


51) What is Auto_Open and where should it be declared ?

This subroutine runs automatically when a workbook (or add-in) is opened manually.
It is not run when a workbook is opened programmatically using the Open method.
This subroutine must be placed in a standard code module (not a class module, ThisWorkbook, Sheet1 or Userform).
This has been replaced with the WorkbookOpen event and is only available for backwards compatibility.


52) Can you give the name of 3 workbook level events ?

Workbook.Open - Occurs before the workbook is opened. This should be used instead of the Auto_Open macro.
Workbook.SheetCalculate - Occurs after the user re-calculates any worksheet in this workbook
Workbook.SheetSelectionChange - Occurs when the selection is changed on any worksheets in this workbook


53) Write code to test if a worksheet is a chart sheet ?

??


54) Write code to ask a question and put the answer in cell A1 ?

Range("A1").Value = Application.InputBox("What is your name?") 


55) How would you add a drop-down list to a cell to provide a list of possible values ?

Data Validation


56) Describe the different types of workbook protection ?

Read Only - requires a password to open the workbook
Structure Only -
Windows Only -
Individual Worksheets -


57) What are Form Controls and how are they different from ActiveX controls ?

Form controls were added in Excel 5.0. ActiveX controls were added in Excel 97.
Form controls are very basic and can be placed on charts. ActiveX controls are more complicated and cannot be placed on charts.
Both types of controls can be placed on worksheets.
Both types can link to cells and ranges on a worksheet.


58) Can you describe the Evaluate Formula dialog box ?



59) What is PowerPivot ?



60) What is a Slicer and when would you use one ?

Added in 2010.


61) Can you give the name of 4 different built-in chart types ?



62) Can you give the name of any new built-in chart types that were introduced in Excel 2016 ?

Box Whisker, Funnel, Histogram, Pareto, Sunburst, Treemap, Waterfall


63) How do you find the last populated cell on a worksheet ?

Using the shortcut key (Ctrl + End).

ActiveSheet.UsedRange.Rows.Count 
ActiveSheet.UsedRange.Columns.Count
Range("A1048576").End(xlUp).Row


64) What is the Custom UI Editor and when would you use it ?

This is a standalone program that can be installed to help you create and modify the ribbon UI in workbooks and add-ins.



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Prev