Below are some important Excel VBA interview questions which are asked in most MNC company interviews for beginners or professionals.
31. Give technical reasons which made Microsoft withdraw its support for VBA in Mac?
The reasons which made Microsoft drop its support to VBA are as follows, Microsoft visual basic relies heavily on machine code which was written for Power PC architecture. Also it would take another two years for developing VBA support for its architecture. It also states that Microsoft will incorporate VBA in the next script of office release for Mac.
32. What is a volatile function?
Volatile functions are a type of function that will always recalculate. That means whenever Excel needs to calculate any part of the worksheet, those cells containing volatile functions will also calculate.
33. Give some examples of Volatile function.
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()
Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()
Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.
A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:
INDEX(), ROWS(), COLUMNS(), AREAS()
and CELL("Filename") IS volatile although a MSKBN article says its not.
One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:
=SUMIF(A1:A4,">0",B1) is volatile whereas =SUMIF(A1:A4,">0",B1:B4) is not volatile.
34. How do you make a UDF volatile?
By adding Application.Volatile statement to it. It must be the first line of your User Defined Function.
35. Is it possible to apply 'Application.Volatile(False)' to a volatile public function like INDEX and make it not volatile?
Actually INDEX is not a volatile function, even though some MicroSoft documentation says it is. Anyway no its not possible to apply Application.Volatile(False) to a built-in Excel function except by duplicating what the built-in function does inside a UDF.
36. What is Excel dependency tree?
Dependency trees are excel way of minimizing the calculation by tracking what has changed since last calculation. It allows Excel to recalculate only:
* Formulae/Names that have changed.
* Formulae containing Volatile Functions
* Formulae dependent on changed or volatile formulae or cells or names.
Excel determines dependencies by looking at the cells referred to by each formula and by the argument list of each function.
Dependency trees are immediately updated whenever a formula is entered or changed.
To force the dependency trees to be rebuilt and all formulae calculation use CTRL+ALT+SHIFT+F9.
37. What are keyboard shortcuts and their equivalent VBA methods for Formulae calculation and building Excel dependency trees?
Shortcut Combination VBA Equivalent Meaning
F9 Application.Calculate Recalculate
Ctrl+Alt+F9 Application.CalculateFull Full Calculation
Ctrl+Alt+Shift+F9 Application.CalculateFullRebuild Rebuild Excel Dependency Tree and Full Calculation
Shift+F9 Sheets(1).Calculate calculate Worksheet
38. What does Range.Dirty used for ?
To add the specified cells to the list of cells requiring calculation at the next recalculation.
39. How do you check the Calculation state ?
Pretty simply, using the Application.CalculationState property which tells if calculation has completed ( xlDone ), is pending ( xlPending) , or is in process ( xlCalculating ).
40. How do you define Excel Calculation Interruption Key to stop the calculation?
Using Application.CalculationInterruptKey= XlAnyKey | XLEscKey | XlNokey.
Remember using XlNokey, calculation cannot be interrupted.
More Questions & Answers :-
Part1 Part2 Part3 Part4 Part5
31. Give technical reasons which made Microsoft withdraw its support for VBA in Mac?
The reasons which made Microsoft drop its support to VBA are as follows, Microsoft visual basic relies heavily on machine code which was written for Power PC architecture. Also it would take another two years for developing VBA support for its architecture. It also states that Microsoft will incorporate VBA in the next script of office release for Mac.
32. What is a volatile function?
Volatile functions are a type of function that will always recalculate. That means whenever Excel needs to calculate any part of the worksheet, those cells containing volatile functions will also calculate.
33. Give some examples of Volatile function.
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()
Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()
Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.
A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:
INDEX(), ROWS(), COLUMNS(), AREAS()
and CELL("Filename") IS volatile although a MSKBN article says its not.
One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:
=SUMIF(A1:A4,">0",B1) is volatile whereas =SUMIF(A1:A4,">0",B1:B4) is not volatile.
34. How do you make a UDF volatile?
By adding Application.Volatile statement to it. It must be the first line of your User Defined Function.
35. Is it possible to apply 'Application.Volatile(False)' to a volatile public function like INDEX and make it not volatile?
Actually INDEX is not a volatile function, even though some MicroSoft documentation says it is. Anyway no its not possible to apply Application.Volatile(False) to a built-in Excel function except by duplicating what the built-in function does inside a UDF.
36. What is Excel dependency tree?
Dependency trees are excel way of minimizing the calculation by tracking what has changed since last calculation. It allows Excel to recalculate only:
* Formulae/Names that have changed.
* Formulae containing Volatile Functions
* Formulae dependent on changed or volatile formulae or cells or names.
Excel determines dependencies by looking at the cells referred to by each formula and by the argument list of each function.
Dependency trees are immediately updated whenever a formula is entered or changed.
To force the dependency trees to be rebuilt and all formulae calculation use CTRL+ALT+SHIFT+F9.
37. What are keyboard shortcuts and their equivalent VBA methods for Formulae calculation and building Excel dependency trees?
Shortcut Combination VBA Equivalent Meaning
F9 Application.Calculate Recalculate
Ctrl+Alt+F9 Application.CalculateFull Full Calculation
Ctrl+Alt+Shift+F9 Application.CalculateFullRebuild Rebuild Excel Dependency Tree and Full Calculation
Shift+F9 Sheets(1).Calculate calculate Worksheet
38. What does Range.Dirty used for ?
To add the specified cells to the list of cells requiring calculation at the next recalculation.
39. How do you check the Calculation state ?
Pretty simply, using the Application.CalculationState property which tells if calculation has completed ( xlDone ), is pending ( xlPending) , or is in process ( xlCalculating ).
40. How do you define Excel Calculation Interruption Key to stop the calculation?
Using Application.CalculationInterruptKey= XlAnyKey | XLEscKey | XlNokey.
Remember using XlNokey, calculation cannot be interrupted.
More Questions & Answers :-
Part1 Part2 Part3 Part4 Part5
No comments:
Post a Comment