If I will conduct an interview, I'll not probe your technical knowledge only but also how much logical and reasoning thinking you possess along with some programming etiquette.
First interviewer sees your general attitude towards programming and will try to calm down your blood pressure. While it seems very easy this is the biggest hurdle. He may give you a very easy program to write like sorting an array, coloring the sheet tabs, etc. The objective is not to see your technical knowledge but your programming behavior. Such programs are so obvious and easy that expectations are not about writing the program but to see comments, indentation, using meaningful variable names, declaring variables(which one don't needs to but considered a great practice), using Option Explicit, Debugging style etc. To know that good you're able to communicate your logic with other developers. This is very helpful for the team and is considered a good practice with fruits returned in long term.
Secondly, he will try to test your knowledge of Excel Object Model. They may ask you to sort and filter a range, or clean out duplicate data from a list, consolidate data from multiple files, import data from MS access databases etc . He will look for knowledge of and ability to work with fundamental Excel objects such as Workbook, Worksheet, Range etc; also he will definitely want to see that you neither use "Macro Recorder" nor macro recorder style code using Select and Activate everywhere.
Thirdly he would be interested in your excel knowledge as well as how VBA works. Like pivots, charts, UDF, volatile functions(Can you write code that runs every time the worksheet changes?”) etc. For list of 50 such ORAL Interview VBA questions, click here.
Lastly, the questions would be job specific that if its a consulting firm questions will revolve around statistical models, SAS etc, in case of bank - financial dashboards,KPI etc. and in case of capacity management or databases they'll revolve around ADO, Basic SQL etc.
Good interviewers put “Knowledge of Excel” above “Knowledge of Excel VBA” as in their opinion, the latter is more straightforward and easier to learn but it’s harder to find people who are good at writing efficient Excel formulas(esp. Array Formulas) as often formulas can yield a far more efficient solution – faster by several orders of magnitude.
Excel VBA is a very vast field but interview questions revolve around common things. Interviewer may ask some stupid or Out-of-the-box question to worry you a little, may give you too less time to write a macro or do anything to worry about. YOU DON'T NEED TO WORRY, it's ok if you don't know answers to some questions. Maintain NEVER GIVE UP attitude and YOU'LL SUCCEED.
WISH YOU ALL THE BEST !