Below are some important Excel VBA interview questions which are asked in most MNC company interviews for beginners or professionals.
11. How to hide a worksheet so that a user cannot unhide it?
Use Sheet's visible property and set it to xlSheetVeryHidden . For eg. Sheets(1).Visible = xlSheetVeryHidden will super hide the first worksheet of the workbook.
12. Union is used for _____________ ?
To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For eg. Set nrange = Union(rng1,rng2)
13. Which are the 2 macro languages and which do you use ?
XLM (used in Excel 97 or before) and VBA(used for 2000 and after). Obviously, VBA is in use these days.
14. Can you lock cells such that only specific users can modify them?
There is a option "Allow users to edit ranges" can be used for this purpose.
15. How can you add a drop-down list to a cell so the user can choose a value from the list?
Using 'Data Validation'.
16. How can you increase the number of rows in a worksheet?
No one can't. They're fixed as 65536(2^16) in Excel 2003 or before and 1048576(2^20) in Excel 2007 & Excel 2010.
17. How can you increase the number of columns in a worksheet?
No one can't. They're fixed as 256(2^8) in Excel 2003 or before and 16384(2^14) in Excel 2007 & Excel 2010.
Ques. 18. How will you distribute a workbook such that it can't be copied using macro or anything?
We can create a workbook which cannot be modified but can not create a workbook which can't be copied.(It depends on system security, it has nothing to do with Excel or VBA)
19. Your colleague created a dashboard and when you enter a value, it appears with two decimal places. For example, when you enter 265 it shows up as 2.65. What's wrong?
By chance Excel's fixed-decimal mode was turned on. To return to normal,
Excel 2003 -->
Click Tools and then Options to display the Options dialog box.
Then click the Edit tab and remove the check mark from the "Fixed decimal " option.
Excel 2007 -->
Click Office button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.
Excel 2010 -->
Click File button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.
Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.
20. How can you prevent a user for adding or deleting sheets?
You need to protect the workbook's structure.
Excel 2003 -->
Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked.
Excel 2007/2010 -->
Go to Review --> Click 'Protect Workbook' --> Click 'Protect Structure and Windows'
If you specify a password, that password will be required to unprotect the workbook. When a workbook's structure is protected, the user may not:
* Add/Delete a sheet
* Hide/Unhide a sheet
* Rename a sheet
* Move a sheet
More Questions & Answers :-
Part1 Part2 Part3 Part4 Part5
11. How to hide a worksheet so that a user cannot unhide it?
Use Sheet's visible property and set it to xlSheetVeryHidden . For eg. Sheets(1).Visible = xlSheetVeryHidden will super hide the first worksheet of the workbook.
12. Union is used for _____________ ?
To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For eg. Set nrange = Union(rng1,rng2)
13. Which are the 2 macro languages and which do you use ?
XLM (used in Excel 97 or before) and VBA(used for 2000 and after). Obviously, VBA is in use these days.
14. Can you lock cells such that only specific users can modify them?
There is a option "Allow users to edit ranges" can be used for this purpose.
15. How can you add a drop-down list to a cell so the user can choose a value from the list?
Using 'Data Validation'.
16. How can you increase the number of rows in a worksheet?
No one can't. They're fixed as 65536(2^16) in Excel 2003 or before and 1048576(2^20) in Excel 2007 & Excel 2010.
17. How can you increase the number of columns in a worksheet?
No one can't. They're fixed as 256(2^8) in Excel 2003 or before and 16384(2^14) in Excel 2007 & Excel 2010.
Ques. 18. How will you distribute a workbook such that it can't be copied using macro or anything?
We can create a workbook which cannot be modified but can not create a workbook which can't be copied.(It depends on system security, it has nothing to do with Excel or VBA)
19. Your colleague created a dashboard and when you enter a value, it appears with two decimal places. For example, when you enter 265 it shows up as 2.65. What's wrong?
By chance Excel's fixed-decimal mode was turned on. To return to normal,
Excel 2003 -->
Click Tools and then Options to display the Options dialog box.
Then click the Edit tab and remove the check mark from the "Fixed decimal " option.
Excel 2007 -->
Click Office button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.
Excel 2010 -->
Click File button on Top-Left corner and click 'Excel Options'.
Go to Advanced and Uncheck 'Automatically insert a decimal point' option.
Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.
20. How can you prevent a user for adding or deleting sheets?
You need to protect the workbook's structure.
Excel 2003 -->
Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked.
Excel 2007/2010 -->
Go to Review --> Click 'Protect Workbook' --> Click 'Protect Structure and Windows'
If you specify a password, that password will be required to unprotect the workbook. When a workbook's structure is protected, the user may not:
* Add/Delete a sheet
* Hide/Unhide a sheet
* Rename a sheet
* Move a sheet
More Questions & Answers :-
Part1 Part2 Part3 Part4 Part5
No comments:
Post a Comment