Excel VBA Interview Question and Answer

1. What is VBA?

Answer:

VBA (Visual Basic for Applications) is a programming language developed by Microsoft to automate tasks in Excel and other Microsoft Office applications.

2. How do you enable the Developer tab in Excel?

Answer:

Go to the "File" tab, click on "Options," select "Customize Ribbon," and check the box for "Developer" in the list of Main Tabs.

3. What is a macro in Excel?

Answer:

A macro is a set of recorded actions or VBA code that automates repetitive tasks in Excel.

4. How do you record a macro in Excel?

Answer:

Go to the "Developer" tab, click on "Record Macro," perform the desired actions, and click "Stop Recording" when finished.

5. How do you assign a macro to a button in Excel?

Answer:

In the "Developer" tab, click on "Insert" in the Controls group, select a button shape, and assign the macro to it using the "Assign Macro" dialog box.

6. How do you write a VBA code to insert a new worksheet?

Answer:

Use the following code: Worksheets.Add

7. What is the difference between "Sub" and "Function" in VBA?

Answer:

A "Sub" procedure performs an action but does not return a value, while a "Function" procedure returns a value.

8. How do you declare a variable in VBA?

Answer:

Use the "Dim" keyword, followed by the variable name and its data type. For example: Dim myVariable As Integer

9. What is the purpose of the "Range" object in VBA?

Answer:

The "Range" object represents a cell, a range of cells, or a named range in Excel.

10. How do you reference a cell in VBA?

Answer:

Use the following syntax: Range("A1") or Cells(1, 1), where the first argument represents the row and the second argument represents the column.

11. How do you loop through cells in a range using VBA?

Answer:

Use a "For Each" loop with the "Range" object. For example:

12. How do you open another workbook using VBA?

Answer:

Use the following code: Workbooks.Open("C:\Path\to\Workbook.xlsx")

13. How do you save a workbook using VBA?

Answer:

Use the following code: ActiveWorkbook.Save

14. How do you add a chart to a worksheet using VBA?

Answer:

Use the following code: ActiveSheet.Shapes.AddChart2(240, xlColumnClustered).Select

15. How do you delete a worksheet using VBA?

Answer:

Use the following code: Application.DisplayAlerts = False: Worksheets("Sheet1").Delete: Application.DisplayAlerts = True

16. How do you find the last used row in a column using VBA?

Answer:

Use the following code: lastRow = Cells(Rows.Count, 1).End(xlUp).Row

17. How do you copy data from one worksheet to another using VBA?

Answer:

Use the following code: Worksheets("Sheet1").Range("A1:B10").Copy Destination:=Worksheets("Sheet2").Range("C1")

18. How do you add a new row to a worksheet using VBA?

Answer:

Use the following code: Rows(5).Insert Shift:=xlDown

19. How do you filter data in a worksheet using VBA?

Answer:

Use the following code: Range("A1:D10").AutoFilter Field:=1, Criteria1:="Apple"

20. How do you format cells in VBA?

Answer:

Use the following code: Range("A1").Font.Bold = True

21. How do you add a comment to a cell using VBA?

Answer:

Use the following code: Range("A1").AddComment "This is a comment"

22. How do you protect a worksheet using VBA?

Answer:

Use the following code: Worksheets("Sheet1").Protect Password:="mypassword"

23. How do you find and replace values in a worksheet using VBA?

Answer:

Use the following code: Cells.Replace What:="OldValue", Replacement:="NewValue", LookAt:=xlWhole, MatchCase:=False

24. How do you create a pivot table using VBA?

Answer:

Use the following code: Worksheets("Sheet1").PivotTableWizard

25. How do you close a workbook without saving changes using VBA?

Answer:

Use the following code: ActiveWorkbook.Close SaveChanges:=False