Questions

1) What is the difference between the file extensions '.xlsx' and '.xlsm' ?
.xlsx - an XML based default file format.
.xlsm - an XML based file format that can contain macros.


2) What is the '.xlsb' file format ?
This is a binary based file format that can contain macros but not ribbon customisations.


3) Are there any advantages or disadvantages to saving a workbook with the '.xlsb' file extension ?
*) File size might be smaller
*) Read from and Write to is faster
*) Formulas larger than 8192 characters can be saved
*) The individual files contained in the xml zip package are compressed binary files and not readable xml.
*) Power Query cannot read data saved in an XLSB workbook.


4) Is there a way to close all my open workbooks at once without having to close them one at a time ?
Yes. Hold down Shift and click on the X (cross) in the top right corner of one of the open workbooks (or windows).


5) What is the largest Excel file you can have ?
Either for a single workbook or a combination of open workbooks.
32 Bit - Excel 2013 / 2016 - 2 GB of memory.
64 Bit - Excel 2013 / 2016 - no hard limit. Workbook size is limited only by available memory and system resources.


6) What changes could you make to reduce the file size of a workbook ?
*) Check the last used cell on each worksheet and delete any redundant blank rows and columns.
*) Check for hidden worksheets and remove out of date sheets.
*) Compress or remove images and pictures.
*) Reduce the size of pivot tables.
*) Optimise formulas and remove array formulas.
*) Remove any unnecessary formatting and use consistent formatting across worksheets.
*) Remove conditional formatting.


7) What is Freeze Panes ?
This lets you lock the rows and columns so they stay visible when you scroll the worksheet.


8) Is there a quick way to apply the same formatting to multiple worksheets ?
Yes. By grouping your sheets.
You can group sheets by holding down Ctrl and selecting the different tabs.


9) What is the difference between Wrap Text and Merge Cells ?
Wrap Text - when applied will mean that text will automatically appear on multiple lines to fit the column width.
Merge Cells - allows two or more cells to be combined to make one larger cell that can span multiple columns and/or rows.


10) When would you use the Center Across Selection horizontal text alignment ?
This allows you to center text across a selection of cells, without the disadvantages of actually merging the cells.


11) When would you use the Goal Seek data analysis feature ?
This can be used to calculate an unknown value in a given formula.


12) When would you use the Scenario Manager data analysis feature ?
Lets you construct various different scenarios with your data and allows you to perform 'what if' analysis.


13) What is an Excel Data Table ?
Not to be confused with a regular table.
This is a type of What If data 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".


14) What is the shortcut key for applying the percentage format with no decimal places ?
(Ctrl + Shift + %)


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


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


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


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


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


20) Can you describe Co-Authoring ?



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


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


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


24) When entering text into cells what would you press to insert a line break into your cell.
(Alt + Enter)


25) Where is the command located for displaying the Edit Links dialog box ?
Data Tab, Queries & Connections group, Edit Links
File Tab, Info Panel, Edit Links to Files


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


27) When is the Quick Analysis Smart Tag displayed ?
It is displayed everytime you select two or more adjacent cells.


28) What is the Power Query add-in ?
This is a Microsoft add-in that enhances the self-service Business Intelligence experience by simplifying data discovery, access and collaboration.
In Excel 2016 this was replaced with the built-in Get & Transform group on the Data tab.


29) What is PowerPivot ?
This is an add-in that provides an extension to the built-in pivot table functionality.


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


31) Is it possible to prevent a user from adding or removing worksheets ?
Yes. Review > Protect Workbook > Protect Structure and Window.


32) Describe the different types of workbook protection ?
Read Only - requires a password to open the workbook
Structure Only - adding, deleting, hiding, unhiding worksheets
Windows Only - changing size or positions of windows
Individual Worksheets -


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


34) What does a red triangle in the top right corner of a cell indicate ?
This indicates that a comment has been added to the cell.


35) What does a green triangle in the top left corner of a cell indicate ?
This indicates that the Error Checking Smart Tag has identified a potential issue.


36) What is the little square icon that sometime appears in the bottom right corner of a cell ?
This is the Smart Tag.


37) What is 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.
A full calculation is always performed when you open a workbook last saved in a earlier version of Excel.


38) When should you change your calculation option to Manual ?
You should switch to Manual calculation when you want to be able to make changes immediately without waiting.
For workbooks that take more than a couple of seconds to recalculate.
If not then there will be a delay whenever anything changes.


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


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


41) Is there a way to interrupt the recalculation if it is taking too long ?
Yes. Pressing the ESC key should (in most cases) interrupt the calculation.


42) Is it possible to prevent the calculation from being interrupted ?
Yes. There are two ways this can be achieved:
Using the CalculationInterrupting property.

Application.CalculationInterrupting = xlCalculationInterruptKey.xlNoKey. 

If you are running from VBA using the Application OnTime.

Application.OnTime MacroName 

43) Is the Calculation Setting saved at the workbook level or the Excel session level ?
All these settings are saved at the workbook level but are applied at an application/session level.
The calculation settings are taken from the first workbook you open (in that session) and then ignored in subsequent workbooks.


44) What changes could you make to speed up workbook recalculation ?
The speed taken to update depends on the number of cell references, the calculation operations and the efficiency of the 'formulas and 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


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

46) What does the Range.Calculate method do ?
Smart Recalculation of the specified range.

Range("A1:D20").Calculate 

47) What does the Range.CalculateRowMajorOrder method do ?
Smart Recalculation of the specified range ignoring any forward references or within range dependencies.

Range("A1:D20").CalculateRowMajorOrder 

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

49) Is there a way to check the calculation state ?
Yes. You can use the CalculateState property.

Application.CalculationState 

50) What does the Workbook.ForceFullCalculation property do ?

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.


51) When would you use the Workbook.ForceFullCalculation property ?
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.


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

Application.OnKey "{F9}", "HandleF9" 
Application.OnKey "+{F9}", "HandleShiftF9"
Application.OnKey "^+{F9}", "HandleCtrlShiftF9"
Application.OnKey "%^+{F9}", "HandleAltCtrlShiftF9"

53) What is a Circular Reference ?
A circular reference will occur when a formula directly or indirectly refers to its own cell.
Excel calculates circular references sheet by sheet without considering dependencies.
If your circular references span multiple worksheets you can get very slow calculation.


54) Can you think of any scenarios when a circular reference is required ?



55) Can you explain what Iterative Calculation means ?
Also known as repetitive calculation.
One of the most common ways of accommodating circular references is to increase the number of iterations.


56) How would you check if a workbook contains any Circular References ?
Switch off the (Excel Options, Formulas)("Enable Iterative Calculation") option.
Excel will provide a warning message if it encounters any circular references.


57) What is the Range.Dirty method ?
Designates a range to be recalculated the next time a recalculation is performed.

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

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


59) What is a Formula ?
A formula is a way of performing a calculation using data in your workbook.


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


61) What is the difference between a Formula and an Array Formula ?
Formula - lets you perform a mathematical calculation in a single cell.
Array Formula - lets you perform complicated mathematical calculations in a single cell. These must be entered using (Ctrl + Shift + Enter).


62) Is there a quick way to see the formulas rather than the values ?
(Ctrl + ') - This is a single left quote, not an apostrophe.


63) What is the difference between Array Formulas and Dynamic Array Formulas ?



64) What is a Worksheet Function ?
A function provides a convenient way to use a common calculation.


65) What is the quickest way to add up a column of numbers ?
*) AutoSum - clicking on this command will add the SUM worksheet function below the current region
*) SUM - using this worksheet function manually.
*) AutoCalculate - highlight the cells and look in the status bar for the quick total.


66) Can you describe the IF function ?
It checks whether certain conditions are true or false.

=IF(A1 > 100, "Display True", "Display False") 

67) Can you describe the IFS function ?
Added in Excel 2019 to return a value based on multiple conditions.


68) What is the difference between the COUNT function and the COUNTA function ?
COUNT - Returns the number of non blank cells in a cell range.
COUNTA - Returns the number of non blank cells in a cell range, including logical values and text.


69) Can you describe the CONCAT function ?
Added in Excel 2019 to replace the CONCATENATE function.
This function is equivalent to the ampersand (&) character and can be used to join several strings together.

=CONCAT(A1," ",A2) = "A1value A2value" 

70) Using the NOW function inserts the current date and time. Is there a way to enter this information so it does not automatically update ?
Yes. Use these shortcut keys instead:
(Ctrl + ;) - inserts the current date
(Ctrl + Shift + ;) - inserts the current time
(Ctrl + ;) space (Ctrl + Shift + ;) - inserts the current date and time


71) Can you describe the OFFSET function ?
This function is volatile and will change everytime a cell on the worksheet is calculated.
Returns the cell value which is an offset from a given cell reference.

OFFSET(reference, rows, cols [,height] [,width]) 

72) Can you describe the MATCH function ?
Returns the position of a value in a list, table or cell range.

MATCH(lookup_value, lookup_array [,match_type]) 

73) Can you describe the INDEX function ?
Returns the value from a cell range which is the intersection of a row and a column.

INDEX(reference, row_num [,column_num] [,area_num]) 

74) Can you describe the COUNTIF function ?
Returns the number of non blank cells that satisfies one condition.
Often referred to as conditional counting.

COUNTIF(range, criteria) 

75) Can you describe the SUMIFS function ?
Returns the total value of the numbers that satisfies multiple conditions.
Often referred to as conditional summing.

SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..]) 

76) Can you describe the INDIRECT function ?
This function is volatile and will change everytime a cell on the worksheet is calculated.
Returns the text string of the contents of a given cell reference as a text string.

=INDIRECT(ref_text [,a1]) 

77) What is the difference between the SUBSTITUTE function and the REPLACE function ?
SUBSTITUTE - returns the text string with a substring substituted for another substring.
REPLACE - this should be used when you want to replace text that occurs in a specific location in a text string.

SUBSTITUTE(text, old_text, new_text [,instance_num]) 
REPLACE(old_text, start_num, num_chars, new_text)

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


79) 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)
=SUMIFS()

80) When would you use the VLOOKUP function ?
When you want to match a value in the first column of a table and return a specific item in that row.


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

VLOOKUP(lookup_value, table_array, colum_index [,range_lookup]) 
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 for the value you want returned
range_lookup - (optional) whether to find an exact match

82) Can you describe some of the common problems that people experience when using the VLOOKUP function ?
This function defaults to approximate match (not exact match)
The value you are looking up must be in the first column of your data table.
The column index number will be broken is you insert or delete columns in your data table.
Forgetting to make the table_array absolute references (using $) before dragging down a formula.


83) What function would you use if you want to match a value in the first row of a table and return a specific item in that column ?
HLOOKUP - returns the value in a row that matches a value in the top row of a table.


84) Can you describe the XLOOKUP function ?

XLOOKUP(lookup_value, lookup_array, return_array [,if_not_found] [,match_mode] [,search_mode]) 
lookup_value - the value to find in the lookup_array
lookup_array -
return_array -
if_not_found -
match_mode -
search_mode -

85) When would you use a combination of the INDEX and MATCH functions ?
If you wanted to lookup a value that was not in the first column.

INDEX('col_return', MATCH('match_item', 'col_data',0), 1)  

86) When would you use a combination of the OFFSET and MATCH functions ?
You should try not to use this combination because OFFSET is a volatile function and using it is very inefficient.
Always use INDEX and MATCH instead.

OFFSET('col_header', MATCH('col_data','table',0) -1, col_offset, 1, 1) 

87) What is a Named Range ?
A named range is a short text description that can be used instead of the cell address to refer to individual cells or ranges of cells.


88) What are the advantages of using Named Ranges ?
You can insert or delete cells, rows and columns and the cell references remain intact.


89) What is the difference between a Workbook Level named range and a Worksheet Level named range ?
Workbook Level - These can be referenced from any worksheet in the workbook, without prefixing.

=Name_Wbk 

Worksheet Level - These are intended to only be used on one specific worksheet although you can reference them from other worksheets by prefixing the name of the worksheet.


90) What type of named range is created when you use the Name Box ?
Typing into the Name Box creates a named range with workbook level scope.
You can create worksheet level scope if you proceed it with the name of the worksheet and an exclamation mark.

=Sheet1!MyWorksheetLevel 

91) What is a Dynamic Named Range ?
A dynamic named range expands automatically when more data is added to the range.
This type of named range uses a combination of the worksheet functions OFFSET and COUNTA.

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$1:$B$100),1) 

92) Write the code to populate an array with the values in a Named Range ?
The following lines are all equivalent.

Dim arValues As Variant 
arValues = Range(NamedRange).Value
arValues = Application.Evaluate[NamedRange].Value
arValues = [NamedRange] '// since value is the default

93) What is a Chart ?
A chart provides a visual representation of data found in a table.


94) What is the quickest way to create a chart ?
(F11) - highlighting the source data and pressing F11 will insert a chart sheet (not an embedded chart) defaulting to a column chart.


95) Can you give the name of 4 different built-in chart types ?
Column, Line, Pie, Area


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


97) What is a Table ?
Defining a table gives you an easy way to manage and analyze your data.
You can have more than one table on a worksheet.


98) Can you describe the advantages of using a table rather than a named range ?
Columns can be referenced using column names in a Table but in a Named Range you have to use an index number.
Sorting and Filtering can be easily done using the drop-down menus in a Table but in a Named Range you have to use the Ribbon commands.
Row shading and totals can be quickly applied to tables.
Tables can contain calculated columns that will populate automatically.


99) How can you create a table ?
??


100) What is the shortcut key for togglying the autofilter on a table ?
(Ctrl + Shift + L)


101) What are Structured References ?
These can be used in conjunction with tables.
When you create a table Excel automatically assigns a name to the table and to each column.
You can then use these names in your formulas instead of cell references.
The combination of table and column names is called a structured reference.


102) What is a Pivot Table ?
A pivot table lets you summarise, analyze, explore and present your data.
They are highly flexible and can be quickly adjusted depending on how you need to display your results.


103) Is it possible to create a pivot table using a dynamic named range ?
Yes. Create a dynamic named range that contains your data and then reference that when you create the pivot table.


104) Is it possible to use data from more than one worksheet to create a pivot table ?
Yes. As long as the data is in the same workbook.


105) Which event can you use to check if a pivot table has been modified ?

Application.SheetPivotTableUpdate 
Workbook.SheetPivotTableUpdate
Worksheet.PivotTableUpdate

106) What are the three different report layout types for a pivot table ?
Compact -
Outline -
Tabular -


107) How would you disable automatic sorting in a pivot table ?
Goto > More Sort Options > Right Click 'pivot tables' > Sort Menu > More Options > sort automatically


108) How would you prevent column widths from changing automatically when a pivot table is refreshed ?
pivot table options > enable preserve formatting > auto format


109) Can you display text in the data area of a pivot table ?
??


110) What is a Slicer and when would you use one ?
Added in 2010.
A slicer is an embedded object that provides a visual interface for filtering data in Tables, Pivot Tables and Cube Functions.


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


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


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


114) Can you outline the steps to create an Excel add-in (.xlam) ?
Create a new workbook
Add the necessary VBA code.
Save the workbook as an Excel Add-in (.xlam)
You can add two optional event handlers if you need to (AddinInstall and AddinUninstall)


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


116) Can you assign a macro to a shape ?
Yes. Right click and select Assign Macro


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


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

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


120) Is it possible to prevent a user from copying cell values from a worksheet ?
Yes. One way to prevent the cells from being selected is by unlocking all the other cells and then protecting the worksheet by unticking 'select locked cells'.
Another way is to use VBA to reset the CutCopyMode property. This doesn't work if macros are disabled.

Sub Workbook_SheetSelectionChange(ByVal sh As Object, _ 
                                  ByVal Target As Range)
   Application.CutCopyMode = False
End Sub

121) What is the difference between ThisWorkbook and ActiveWorkbook ?

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

122) What is the difference between UsedRange and CurrentRegion ?
UsedRange - the cell range of all the used cells on the worksheet.
CurrentRegion - the cell range inside blank rows and columns.

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

123) What is the difference between Range.Value and Range.Value2 ?
The Value2 Property does not recognise (and convert into) the VBA Currency data type or the VBA Date data type.
Range.Value - This returns the formatted value.
Range.Value2 - This returns the actual numerical value without formatting.


124) Is it possible to hide a worksheet so the user cannot unhide it ?
Yes. This can only be done via the Visual Basic Editor, Properties window.

Sheets(2).Visible = xlSheetVisibility.xlSheetVeryHidden 

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

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

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

127) Write a User Defined Function that accepts two arguments and returns the lowest common denominator ?

Public Function LOWEST_COMMON_DENOM(ByVal Number1 As Double, _ 
                                    ByVal Number2 As Double) As Double
   LOWEST_COMMON_DENOM = Application.WorksheetFunction.LCM(Number1, Number2)
End Function

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


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

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

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

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

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

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

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

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

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

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



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrev