VBA – FAQ in Interviews

What is VBA?

  1. VBA stands for Visual Basic for Application
  2. VBA is a programming language to manipulate MS office objects.
  3. VBA is not an OOP language like C++, Java. But we can create our customized object
  4. It doesn’t create exe. It uses interpreter to execute the code line by line

What is Application?

A in VBA stands for application which can be any of these 1. MS Excel, 2. MS Access, 3. MS Word, 4. MS PowerPoint, 5. MS outlook.

What is an object?

  1. Object is an entity that perform task.
  2. Each object has its own properties and methods.
  3. Objects can perform whatever written in its class
  4. Everything you see is an object ( Even you are an object of human class too)

And what is a Class?

  1. Class is blueprint of an object.
  2. It has all the definition of what object would perform.
  3. It has properties and methods of Objects.
  4. Countless objects can be created from a class.
  5. Class Example
    You have two legs –> Property
    You have two eyes –> Property
    You have Mouth –> Property
    You can walk, you can see, you can talk –> Methods
    You are a human

What are properties and methods?

  1. Properties –> Which can return or store a value
    1. Properties can be read only / Read Write only
      Example –> Range(“A1”).value, Thisworkbook.Name
  2. Methods –> Which can perform a task Methods may sometime need permission to perform task
    1. Example –> Sheet1.delete, Range.Merge

What are macros?

  1. MS excel provides a way to records the manual steps.
  2. These recorded steps automatically got saved in VBE (Visual basic Editor).
  3. And these recorded programs are called Macros

So why do we need Visual basic scripting?

  1. Macros –> Massive and Complicated Recorded Operation Script.
  2. When macros are recorded, those are lengthy.
  3. Recorded macro doesn’t do a lot for you.
  4. Recorded macros doesn’t used any programming concepts (loops etc)
  5. But you would often need to record macro to understand the properties or methods of an object.

How many type of procedures we can have in VBA?

We can have three types of procedures in VBA. 1. Sub Procedures, 2. Function Procedures, 3. Event Procedures.

What is difference in Sub and Function procedures?

  1. Function procedures return a value
  2. Sub Procedures perform a task.

What is a variable?

  1. Variable is memory holder we use to save our values.
  2. Variable can be public and Private.
  3. Variable must have a data type.

What are the rules to declare a variable?

  1. First character must be an alphabet.
  2. No Special characters are allowed except underscore ( _ )
  3. Max length of a variable is 255
  4. Reserved keyword cannot be used as a variable name.
  5. Keep in mind the ambiguity.
  6. Must have a data type.
  7. Use a meaningful name for your variable
  8. Use Hungarian notation for your variable.
    Example

    1. Dim lngCounter as long
    2. Dim strFileData as string

What are constants?

Unlike variables, value of constants cannot be changed in runtime. We have to assign a values on constants at the declaration time.

Example
Public Const strCompanyName as string =”My Company Name”

What is data type?

  1. Data type tells, what kind of data is going to be stored in a variable.
  2. Data type validate the data before it gets stored in the memory.
  3. Each data type uses different amount of memory, so be wise to choose.
    1. Long –> Let variable store the numeric data
    2. String –> Let variable store the Text/Numeric/Alphanumeric data
    3. Double –>Let variable store the numeric data in decimals
    4. Byte –>Let variable store the numeric data (1 -255)
    5. Object –> Let variable store an Object (Shapes, Charts, Worksheet etc.)
    6. Variant –> All kind of Data type. (Largest memory consumer)

What are UDTs (user defined types) and how to create them?

  1. UDTs are the data types created by programmers.
  2. It has collection of many existing data types or other UDTs.
  3. These can be scoped as Public or Private

What are parameters?

Parameters or arguments are the variables we pass to the other function, so that called function can use them and work dynamically.

What is call by Reference and call by values?

  1. These two are the type we can pass our variable in another procedure/function.
  2. When we pass ByVal, only value of the variable get pass in the function. It creates another memory for the passes variable for the called function. So, called function cannot change the value of the variable declared in the main program.
  3. When we pass ByRef. It pass the memory address of the variable to other function, thus, called function is able to change the value of the variable declared in main program. ByRef is default in VBA.

What is the difference in For Loop, Do While and Do Until Loop?

  1. For loop is fixed loop we run on numeric values, in this loop we know how many iteration loop is going to do. Do loops are conditional loop, we don’t know how many time these are going to repeat.
    1. Do while is continuing to run when condition is TRUE
    2. Do Until is continuing to run when condition is FALSE.

How to call a procedure from another workbook?

We can use Application.Caller method of VBA to do so.

You still need to learn lot more