Thanks for the humongous response to our 50 Excel VBA Oral Interview questions and 3 Things to remember before you go for an Excel Interview. This really motivated us to bring this article for you. We all Excel lovers and learners are climbing a virtual staircase day-by-day, taking us to somewhere top of the world. Be confident, help others, share your experience and learn new knowledge and experiences out of it.
In recent times, we have seen a great demand for Business Analysts, Data Analysts and other jobs which require good skills in Excel and VBA. Excel is used in every company and at home, while beginner misunderstoods it as a simple tool, amateurs, professionals and experts are still learning it, like you, me and everyone else and diving in the ocean of Excel to find new pearls everyday.
In this article, we've presented some VBA Macro programs which are generally asked in VBA Interviews to check the skills of job aspirants. These VBA Interview questions ranges from logical problems, charts, pivots, event macros to creation of UDFs.
Tip for Interviewers: You must do slight change to these questions, as many interviewee knows this website. Wish you all the best in selecting BEST candidate.
Tip for Interviewee: Read my tip to interviewers and be ready. Wish you all the best in your future endeavours.
Description:
i. Warn user, if he enters anything except a number between 1 to 10.
ii. Pyramid must look like as presented in image below.
iii. Create a single procedure to achieve this.
Input an alphabet and based on its alphabetic position(A=1, B=2 etc.) create a square grid.
Description:
i. Warn user, if he enters anything except an alphabet from A to Z.
ii. Square grid must look like as presented in image below.
iii. Create a single procedure to achieve this.
Description:
i. Number format of axes should be like 10,20, 30 i.e. no decimals.
ii. Number format of labels should be like 12.1,15.6, 25.5 i.e. one place of decimal.
iii. Create a single procedure to achieve this.
Resize and rotate all charts by 90 degree in clock-wise direction in the given workbook.
Description:
i. Resize all charts of the workbook to the dimensions of chart named as "Main_Chart" in first sheet.
ii. Then, rotate all charts by 90 degree in clockwise direction.
iii. Create a single procedure to achieve this.
Run Charts Slideshow in Excel.
Description:
i. Provide option to go on next chart or exit the slideshow.
ii. Charts must not be displaced from their initial position by the end of slideshow.
iii. Size of charts must not be changed from their initial size by the end of slideshow.
Description:
i. Remove and add the existing calculated field with prompts informing the respective operations.
ii. Perform your operation on provided pivot table and calculated formula field.
iii. Create any number of procedures to achieve this.
Count the number of Pivots and Charts in Active workbook.
Description:
i. Count all the pivot tables and charts of each worksheet in active workbook.
ii. Create a separate sheet for result with number of charts and pivots in each worksheet.
iii. Create a single procedure to achieve this.
Record the user actions on Pivot Table in the provided cells.
Description:
i. Fill the yellow cells in the provided worksheet with changed filter name and time.
ii. Create a single event procedure to achieve this.
Description:
i. Write the VBA macro as if access database and excel file resides in same directory always.
ii. Access Database is a mdb file i.e. Office 2003 format.
iii. Create a single procedure to achieve this.
Create Access Database from provided Excel Workbook.
Description:
i. Each sheet of provided Excel file must represent separate table in Access database.
ii. Access database should be of Office 2003 format i.e. a .mdb file.
iii. Create a single procedure to achieve this.
Create User Defined Function to validate email addresses.
Description:
i. To validate remember it should check exact one @ character.
ii. It must only contain alphanumeric set, underscore(_),hyphen or dash(-) and period/dot(.)
iii. Leftmost and Rightmost character should not be .
iv. Period/dot (.) must appear at least once after the @.
v. There should be either 2 or 3 characters(eg. com, net, org, in, us, uk etc) after the last period (.)
vi. There must be at least one alphanumeric character before @
vii. Create a single UDF procedure to achieve this.
viii. Test your UDF function against the provided dummy e-mail addresses list.
Create a workbook which a user can update and save but not be able to save the updates.
Description:
i. Do not apply worksheet or workbook protection.
ii. If macros enabled, a user can update the workbook and save it.
iii. But in reality, workbook is not saved and even do not warn or prompt anything.
iv. Create a single procedure to achieve this.
Create a UDF to extract first set of alphabets from alphanumeric strings/data.
Description:
i. Extract first set of alphabets from required list.
ii. For eg. extract eXceL from eXceL123iTems.
iii. Create a single procedure to achieve this.
When SEARCH button is clicked,search selected text on Bing in Internet Explorer.
Description:
i. Warn user, if the selection is a blank cell.
ii. Warn user, if the selection is not a range or a single Cell.
iii. Create a single procedure to achieve this.
Write an Excel VBA macro to select the text files through a window and delete them.
Description:
i. Provide file selection window, in which he can see only text files of a folder.
ii. Prompt user for confirmation before actual deletion.
iii. Create a single procedure to achieve this.
In recent times, we have seen a great demand for Business Analysts, Data Analysts and other jobs which require good skills in Excel and VBA. Excel is used in every company and at home, while beginner misunderstoods it as a simple tool, amateurs, professionals and experts are still learning it, like you, me and everyone else and diving in the ocean of Excel to find new pearls everyday.
In this article, we've presented some VBA Macro programs which are generally asked in VBA Interviews to check the skills of job aspirants. These VBA Interview questions ranges from logical problems, charts, pivots, event macros to creation of UDFs.
Tip for Interviewers: You must do slight change to these questions, as many interviewee knows this website. Wish you all the best in selecting BEST candidate.
Tip for Interviewee: Read my tip to interviewers and be ready. Wish you all the best in your future endeavours.
Logical VBA Interview questions
Input a number between 1 to 10 to create pyramid.Description:
i. Warn user, if he enters anything except a number between 1 to 10.
ii. Pyramid must look like as presented in image below.
iii. Create a single procedure to achieve this.
Input an alphabet and based on its alphabetic position(A=1, B=2 etc.) create a square grid.
Description:
i. Warn user, if he enters anything except an alphabet from A to Z.
ii. Square grid must look like as presented in image below.
iii. Create a single procedure to achieve this.
Charts VBA Interview questions
Change the number format of axes and labels of all charts in workbook.Description:
i. Number format of axes should be like 10,20, 30 i.e. no decimals.
ii. Number format of labels should be like 12.1,15.6, 25.5 i.e. one place of decimal.
iii. Create a single procedure to achieve this.
Resize and rotate all charts by 90 degree in clock-wise direction in the given workbook.
Description:
i. Resize all charts of the workbook to the dimensions of chart named as "Main_Chart" in first sheet.
ii. Then, rotate all charts by 90 degree in clockwise direction.
iii. Create a single procedure to achieve this.
Run Charts Slideshow in Excel.
Description:
i. Provide option to go on next chart or exit the slideshow.
ii. Charts must not be displaced from their initial position by the end of slideshow.
iii. Size of charts must not be changed from their initial size by the end of slideshow.
Pivots VBA Interview questions
Write a VBA macro to remove and add an existing calculated field from pivot.Description:
i. Remove and add the existing calculated field with prompts informing the respective operations.
ii. Perform your operation on provided pivot table and calculated formula field.
iii. Create any number of procedures to achieve this.
Count the number of Pivots and Charts in Active workbook.
Description:
i. Count all the pivot tables and charts of each worksheet in active workbook.
ii. Create a separate sheet for result with number of charts and pivots in each worksheet.
iii. Create a single procedure to achieve this.
Record the user actions on Pivot Table in the provided cells.
Description:
i. Fill the yellow cells in the provided worksheet with changed filter name and time.
ii. Create a single event procedure to achieve this.
General VBA Interview questions
Write a VBA macro to import all Tables from provided Access Database to Excel worksheets.Description:
i. Write the VBA macro as if access database and excel file resides in same directory always.
ii. Access Database is a mdb file i.e. Office 2003 format.
iii. Create a single procedure to achieve this.
Create Access Database from provided Excel Workbook.
Description:
i. Each sheet of provided Excel file must represent separate table in Access database.
ii. Access database should be of Office 2003 format i.e. a .mdb file.
iii. Create a single procedure to achieve this.
Create User Defined Function to validate email addresses.
Description:
i. To validate remember it should check exact one @ character.
ii. It must only contain alphanumeric set, underscore(_),hyphen or dash(-) and period/dot(.)
iii. Leftmost and Rightmost character should not be .
iv. Period/dot (.) must appear at least once after the @.
v. There should be either 2 or 3 characters(eg. com, net, org, in, us, uk etc) after the last period (.)
vi. There must be at least one alphanumeric character before @
vii. Create a single UDF procedure to achieve this.
viii. Test your UDF function against the provided dummy e-mail addresses list.
Create a workbook which a user can update and save but not be able to save the updates.
Description:
i. Do not apply worksheet or workbook protection.
ii. If macros enabled, a user can update the workbook and save it.
iii. But in reality, workbook is not saved and even do not warn or prompt anything.
iv. Create a single procedure to achieve this.
Create a UDF to extract first set of alphabets from alphanumeric strings/data.
Description:
i. Extract first set of alphabets from required list.
ii. For eg. extract eXceL from eXceL123iTems.
iii. Create a single procedure to achieve this.
When SEARCH button is clicked,search selected text on Bing in Internet Explorer.
Description:
i. Warn user, if the selection is a blank cell.
ii. Warn user, if the selection is not a range or a single Cell.
iii. Create a single procedure to achieve this.
Write an Excel VBA macro to select the text files through a window and delete them.
Description:
i. Provide file selection window, in which he can see only text files of a folder.
ii. Prompt user for confirmation before actual deletion.
iii. Create a single procedure to achieve this.
Comments