User FAQs


1) What is the quickest way to select all the objects on the active worksheet ?
F5 (GoTo dialog) Special objects.
You can also hold down Ctrl to select individual objects.


2) Can you describe Conditional Formatting ?
This is the automatic formatting of cells based on the values that the cells contain.


3) Can you describe Data Validation ?
This allows you to create restrictions on the type of data that can be entered in a cell.


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


5) Can you describe Outlines ?
The outlining feature lets you quickly group and summarise your data.
Using an outline helps you separate top level rows and columns from the breakdown.
(Alt + Shift + Right Arrow) - groups the selected rows


6) Can you describe Co-Authoring ?



7) Does Excel contain any Track Changes functionality (like Word does) ?
Yes. This functionality can be accessed from the Review Tab, Changes group.


8) Can you provide the names of 6 different number format categories ?
Currency, Accounting, Percentage, Scientific, Date, Text


9) What is the name given to the default number format ?
General - this has no specific number format.


10) What features can be found on the Quick Analysis Smart Tag ?
This smart tag provides quick access to 5 features:
Formatting, Charts, Totals, Tables, Sparklines


11) What is the Quick Access Toolbar ?
This is a customizable toolbar which can be used to contain your most frequently used commands.
This toolbar is independent of the tabs and is accessible from any tab.


12) When is the Fill Handle displayed ?
It is always displayed in the bottom right corner of the active cell (or selection).


13) Can you describe the Evaluate Formula dialog box ?
This lets you see different parts of a nested formula evaluated in the order the formula is calculated.


14) What is a Macro ?
A macro can be defined as lines of code that can be easily run multiple times.
When you have a repetitive task you can use a macro to quickly repeat the steps.


15) How would you run a macro that has been saved in the active workbook ?
Developer Tab, Macros


16) 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.


17) If a VBA macro is running and needs to be interrupted what keys would you press to interrupt it ?
(Escape)
(Ctrl + Break)
(Ctrl + Shift + Break)


18) If a VBA 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


19) What is the difference between ThisWorkbook and ActiveWorkbook ?

Application.ActiveWorkbook     'workbook in the active window.  
Application.ThisWorkbook 'workbook where the code is actually running.

20) 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

21) What is the Application.Union method ?
Returns a range object that represents the union of two or more ranges.

Dim oRange1 As Excel.Range 
Set oRange1 = Application.Union(Range("A1:A5"), Range("C1:C5"))
oRange1.Select

22) What is the Application.Intersect method ?
Returns a range that object that represents the rectangular intersection of two or more ranges.

Dim oRange2 As Excel.Range 
Set oRange2 = Application.Intersect(Range("B1:B5"), Range("A3:E3"))
oRange2.Select

23) 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

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

NewSheet - Occurs before an additional worksheet is added to this workbook. 
Open - Occurs before the workbook is opened.
SheetCalculate - Occurs after the user re-calculates any worksheet in this workbook.

25) Can you give the name of 3 worksheet level events ?

BeforeDoubleClick - Occurs before any cell on the worksheet is double clicked. 
Change - Occurs when any cell on the worksheet has its value changed.
SelectionChange - Occurs when the selection is changed on the worksheet.

26) Can you give the name of 3 application level events ?

WorkbookBeforeClose - Occurs before any open workbook is closed. 
WindowActivate - Occurs when the user moves focus to another window or open workbook.
WorkbookModelChange - Occurs when a data model is updated.

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

If (Sheets(1).Type = xlChart) Then 
If (VBA.TypeName(Sheets(1)) = "Chart Sheet") Then

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

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

29) 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.


30) Modify any code that refers to anything Active.
Avoid using any of these in your macros. The current selection might not always be what you are expecting.

ActiveCell 
ActiveChart
ActiveSheet
ActiveWorkbook

31) Remove any code that selects or activates ranges or objects.
You do not actually have to select or activate something in order to change or manipulate it.

Range("A1:D10").Select 
Sheets(2).Activate

32) VBA - Always refer to named ranges in macros

Range("MyNamedRange"). 

Never use date literals

Dim dtMyDate As Date 
dtMyDate = #02/03/2016#

These dates ignore the regional settings and are always interpreted in US date format (mm/dd/yyyy)


33) VBA - Always read and write data to and from arrays.



© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext