VBA – FAQ in Interviews
- VBA stands for Visual Basic for Application
- VBA is a programming language to manipulate MS office objects.
- VBA is not an OOP language like C++, Java. But we can create our customized object
- It doesn’t create exe. It uses interpreter to execute the code line by line
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.
- Object is an entity that perform task.
- Each object has its own properties and methods.
- Objects can perform whatever written in its class
- Everything you see is an object ( Even you are an object of human class too)
- Class is blueprint of an object.
- It has all the definition of what object would perform.
- It has properties and methods of Objects.
- Countless objects can be created from a class.
- 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
- Properties –> Which can return or store a value
- Properties can be read only / Read Write only
Example –> Range(“A1”).value, Thisworkbook.Name
- Methods –> Which can perform a task Methods may sometime need permission to perform task
- Example –> Sheet1.delete, Range.Merge
- MS excel provides a way to records the manual steps.
- These recorded steps automatically got saved in VBE (Visual basic Editor).
- And these recorded programs are called Macros
- Macros –> Massive and Complicated Recorded Operation Script.
- When macros are recorded, those are lengthy.
- Recorded macro doesn’t do a lot for you.
- Recorded macros doesn’t used any programming concepts (loops etc)
- But you would often need to record macro to understand the properties or methods of an object.
We can have three types of procedures in VBA. 1. Sub Procedures, 2. Function Procedures, 3. Event Procedures.
- Function procedures return a value
- Sub Procedures perform a task.
- Variable is memory holder we use to save our values.
- Variable can be public and Private.
- Variable must have a data type.
- First character must be an alphabet.
- No Special characters are allowed except underscore ( _ )
- Max length of a variable is 255
- Reserved keyword cannot be used as a variable name.
- Keep in mind the ambiguity.
- Must have a data type.
- Use a meaningful name for your variable
- Use Hungarian notation for your variable.
- Dim lngCounter as long
- Dim strFileData as string
Unlike variables, value of constants cannot be changed in runtime. We have to assign a values on constants at the declaration time.
Public Const strCompanyName as string =”My Company Name”
- Data type tells, what kind of data is going to be stored in a variable.
- Data type validate the data before it gets stored in the memory.
- Each data type uses different amount of memory, so be wise to choose.
- Long –> Let variable store the numeric data
- String –> Let variable store the Text/Numeric/Alphanumeric data
- Double –>Let variable store the numeric data in decimals
- Byte –>Let variable store the numeric data (1 -255)
- Object –> Let variable store an Object (Shapes, Charts, Worksheet etc.)
- Variant –> All kind of Data type. (Largest memory consumer)
- UDTs are the data types created by programmers.
- It has collection of many existing data types or other UDTs.
- These can be scoped as Public or Private
Parameters or arguments are the variables we pass to the other function, so that called function can use them and work dynamically.
- These two are the type we can pass our variable in another procedure/function.
- 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.
- 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.
- 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.
- Do while is continuing to run when condition is TRUE
- Do Until is continuing to run when condition is FALSE.
We can use Application.Caller method of VBA to do so.