Below are some important Excel VBA interview questions which are asked in most MNC company interviews for beginners or professionals.
41. What does 'Workbook.ForceFullCalculation' do?
When this property is set to True, dependencies are not loaded at open, the dependency dirty chain is not updated, and every calculation of the workbook is a full calculation rather than a recalculation.
If you have a workbook that has so many complex dependencies that loading the dependencies at workbook open takes a long time or recalculation takes longer than full calculation, you can use this property to force Excel to skip loading the dependencies and always use full calculation. Also if making a change to the workbook takes a long time in manual mode because of the time taken to dirty all the dependencies of the cell being changed, then setting Workbook.ForceFullCalculation to True will eliminate the delay.
* Although this is a workbook property the effect is at Application level rather than workbook level.
* In Excel 2007 setting the property back to False once it has been set to True has no effect on the current Excel session.
* The property is saved and restored with the workbook.
* Once this property has been set to True 'Calculate' shows in the status bar and cannot be removed by using calculation keys such as F9.
42. What are excel dependency tree limits?
There are two limits to the number of dependencies that Excel versions prior to Excel 2007 can track before it must do full calculations instead of recalculations.
* The number of different areas in a sheet that may have dependencies is limited to 65,536.
* The number of cells that may depend on a single area is limited to 8K.
After the workbook has passed these limits, Excel no longer attempts to recalculate only changed cells. Instead, it recalculates all cells at each calculation.
43. Mention those conditions when status bar shows CALCULATE.
There are five known conditions in which the status bar will show CALCULATE:
* The Calculation Option has been set to Manual and the workbook contains uncalculated formulae. Try setting calculation to Automatic (Tools-->Options-->Calculate). Note that Excel sets the calculation mode from the first workbook opened in a session: when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened.
* The Iteration Option is turned on and the workbook contains circular references. Check that turning off Iteration (Tools-->Options-->Calculation) and pressing F9 shows "Circular Reference" in the statusbar.
* You are using Excel 2000 without the SR1 update and have a user-defined function that attempts to define a name and depends on a volatile function: see MSKB Q248179
* You have hit one of Excels limits for tracking dependencies.
* You are using Excel 2007 and have set Workbook.ForceFullCalculation to True
44. What do you know about multi threaded calculation?
Excel 2007 can split calculation across multiple processors or cores. When Excel 2007 loads a workbook, it determines from the operating system how many processors are available and then creates a separate calculation thread for each processor. These threads can then run in parallel. The beauty of this system is that it scales extremely well with the number of processors.
Most workbooks show a significant improvement in calculation speed on a system with multiple cores. The degree of improvement depends on how many independent calculation trees the workbook contains. If you make a workbook that contains one continuous chain of formulas, it will not show any multithreaded calculation (MTC) performance gain, whereas a workbook that contains several independent chains of formulas will show gains close to the number of processors available.
45. How can we dial a phone number?
Shell command present in VBA can be used to start the dialer present in windows operating system. Phone number can be used to connect to your modem. With the use of shell and sendkeys you can dial to your user. Shell starts windows application and sendkeys inform the window to dial according to the keystrokes of the application. A macro can be used to start the cardfile program which activates the auto dialer feature.
46. What do you know about the interpretation features of VBA?
VBA is licensed to Microsoft and this compatible with and only Microsoft products. Code written is compiled by an intermediate language called P-code and this is stored in hosting applications such as Excel, Word and Access. The intermediate code is interpreted by a virtual machine. This code and intermediate language is the exclusive right of Microsoft.
47. Explain about insert module and Goal Seek functions present in VBA?
The chief use of VBA is to make use of its special function which helps in repeated actions. Goal seek function helps to reduce manual entry of the code each and every time. This solves the problem of repeated function entry by automating functions and actions. Sub routines are inserted into the using the VBA editor and command insert module.
48. State the difference between Visual Basic, VB Script and Visual Basic for Applications?
Visual basic is useful if you are planning to develop your programs from scratch.This language helps you in developing Active x controls, exe files, etc.
VB script is a powerful tool, through which you can create small scale applications on web pages, automation applications, etc. Integrated development environment is not present for VB script.
Visual Basic for Applications are very useful in automating your existing application. VB application is useful for developing already existing applications.
49. Write a macro to select all the non-blank cells of Activesheet ?
Sub NonBlankCells()
On Error Resume Next
Union(Cells.SpecialCells(xlCellTypeFormulas, 23), Cells.SpecialCells(xlCellTypeConstants, 23)).Select
If Err.Number <> 0 Then
Cells.SpecialCells(xlCellTypeFormulas, 23).Select
Else
Exit Sub
End If
If Err.Number <> 0 Then
Cells.SpecialCells(xlCellTypeConstants, 23).Select
Else
Exit Sub
End If
On Error GoTo 0
End Sub
50. What is the difference between UsedRange and CurrentRegion properties ?
i) The current region is a range bounded by any combination of blank rows and blank columns.
This property is useful for many operations that automatically expand the selection to include the entire current region, such as the AutoFormat method. This property cannot be used on a protected worksheet.
The UsedRange property is used to select the range of used cells on a worksheet. It returns a Range object that represents the used range on the specified worksheet.
ii) Every non-blank cell got its CurrentRegion and its keyboard shortcut is Ctrl+Shift+Spacebar.
iii) There can be many current regions but there is only one used range in a worksheet.
More Questions & Answers :-
Part1 Part2 Part3 Part4 Part5
41. What does 'Workbook.ForceFullCalculation' do?
When this property is set to True, dependencies are not loaded at open, the dependency dirty chain is not updated, and every calculation of the workbook is a full calculation rather than a recalculation.
If you have a workbook that has so many complex dependencies that loading the dependencies at workbook open takes a long time or recalculation takes longer than full calculation, you can use this property to force Excel to skip loading the dependencies and always use full calculation. Also if making a change to the workbook takes a long time in manual mode because of the time taken to dirty all the dependencies of the cell being changed, then setting Workbook.ForceFullCalculation to True will eliminate the delay.
* Although this is a workbook property the effect is at Application level rather than workbook level.
* In Excel 2007 setting the property back to False once it has been set to True has no effect on the current Excel session.
* The property is saved and restored with the workbook.
* Once this property has been set to True 'Calculate' shows in the status bar and cannot be removed by using calculation keys such as F9.
42. What are excel dependency tree limits?
There are two limits to the number of dependencies that Excel versions prior to Excel 2007 can track before it must do full calculations instead of recalculations.
* The number of different areas in a sheet that may have dependencies is limited to 65,536.
* The number of cells that may depend on a single area is limited to 8K.
After the workbook has passed these limits, Excel no longer attempts to recalculate only changed cells. Instead, it recalculates all cells at each calculation.
43. Mention those conditions when status bar shows CALCULATE.
There are five known conditions in which the status bar will show CALCULATE:
* The Calculation Option has been set to Manual and the workbook contains uncalculated formulae. Try setting calculation to Automatic (Tools-->Options-->Calculate). Note that Excel sets the calculation mode from the first workbook opened in a session: when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened.
* The Iteration Option is turned on and the workbook contains circular references. Check that turning off Iteration (Tools-->Options-->Calculation) and pressing F9 shows "Circular Reference" in the statusbar.
* You are using Excel 2000 without the SR1 update and have a user-defined function that attempts to define a name and depends on a volatile function: see MSKB Q248179
* You have hit one of Excels limits for tracking dependencies.
* You are using Excel 2007 and have set Workbook.ForceFullCalculation to True
44. What do you know about multi threaded calculation?
Excel 2007 can split calculation across multiple processors or cores. When Excel 2007 loads a workbook, it determines from the operating system how many processors are available and then creates a separate calculation thread for each processor. These threads can then run in parallel. The beauty of this system is that it scales extremely well with the number of processors.
Most workbooks show a significant improvement in calculation speed on a system with multiple cores. The degree of improvement depends on how many independent calculation trees the workbook contains. If you make a workbook that contains one continuous chain of formulas, it will not show any multithreaded calculation (MTC) performance gain, whereas a workbook that contains several independent chains of formulas will show gains close to the number of processors available.
45. How can we dial a phone number?
Shell command present in VBA can be used to start the dialer present in windows operating system. Phone number can be used to connect to your modem. With the use of shell and sendkeys you can dial to your user. Shell starts windows application and sendkeys inform the window to dial according to the keystrokes of the application. A macro can be used to start the cardfile program which activates the auto dialer feature.
46. What do you know about the interpretation features of VBA?
VBA is licensed to Microsoft and this compatible with and only Microsoft products. Code written is compiled by an intermediate language called P-code and this is stored in hosting applications such as Excel, Word and Access. The intermediate code is interpreted by a virtual machine. This code and intermediate language is the exclusive right of Microsoft.
47. Explain about insert module and Goal Seek functions present in VBA?
The chief use of VBA is to make use of its special function which helps in repeated actions. Goal seek function helps to reduce manual entry of the code each and every time. This solves the problem of repeated function entry by automating functions and actions. Sub routines are inserted into the using the VBA editor and command insert module.
48. State the difference between Visual Basic, VB Script and Visual Basic for Applications?
Visual basic is useful if you are planning to develop your programs from scratch.This language helps you in developing Active x controls, exe files, etc.
VB script is a powerful tool, through which you can create small scale applications on web pages, automation applications, etc. Integrated development environment is not present for VB script.
Visual Basic for Applications are very useful in automating your existing application. VB application is useful for developing already existing applications.
49. Write a macro to select all the non-blank cells of Activesheet ?
Sub NonBlankCells()
On Error Resume Next
Union(Cells.SpecialCells(xlCellTypeFormulas, 23), Cells.SpecialCells(xlCellTypeConstants, 23)).Select
If Err.Number <> 0 Then
Cells.SpecialCells(xlCellTypeFormulas, 23).Select
Else
Exit Sub
End If
If Err.Number <> 0 Then
Cells.SpecialCells(xlCellTypeConstants, 23).Select
Else
Exit Sub
End If
On Error GoTo 0
End Sub
50. What is the difference between UsedRange and CurrentRegion properties ?
i) The current region is a range bounded by any combination of blank rows and blank columns.
This property is useful for many operations that automatically expand the selection to include the entire current region, such as the AutoFormat method. This property cannot be used on a protected worksheet.
The UsedRange property is used to select the range of used cells on a worksheet. It returns a Range object that represents the used range on the specified worksheet.
ii) Every non-blank cell got its CurrentRegion and its keyboard shortcut is Ctrl+Shift+Spacebar.
iii) There can be many current regions but there is only one used range in a worksheet.
More Questions & Answers :-
Part1 Part2 Part3 Part4 Part5
No comments:
Post a Comment