Created by : Chetna Sharma, Delhi (India)

Logical Question and Answer.

New Excel,  VBA, MS Access,  SQL, SAS and PowerBI batch starting from Saturday, contact 8319560164 for more details.

Click Link for more details.

Q1 - What is VBA?

VBA Stands for Visual Basic for Applications

  1. Its helps us automating Microsoft applications and provide customized soulutions.
  2. It’s a programming language for Microsoft includes: – Excel, PowerPoint, WinWord, MS Access, Visio etc.
  3. It enables you to automate the various process or activity which you do manually in excel like generating report preparing chart & graphs doing calculation etc.
Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q2 - What are macros in Excel?

To automate the various process or activity which we do manually in excel or other application like generating report, preparing chart & graphs doing calculation etc. are called macro.

Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q3 - Explain few basic shortcut of VBA?

Below are some Basic and daily need shortcuts in VBA.

  • Alt + F11   :- Open VBA Coding Screen.
  • Ctr + R      :- Open Project Explore
  • F4               :- Properties of selected objects
  • Ctr + G      :- Immediate Screen
  • F7               :- Coding screen
  • F8               :- Step by step Execution of code
  • F5               :- Run entire VBA coding

 

Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q4 - How we can copy and paste data in one line code?

Below is single line code to copy and paste data

Range(“Source_Range”).copy ([Destination])

  1. Source_Range :- Data we need to copy
  2. Destination      :- Place we need to paste data

For Example :-

Range(“F8:F10”).Copy Range(“A1”)

  1. F8:F10 is as source range
  2. A1 is destination to paste data

Sub Macro_Name()
Sheets(“Sheet1”).Range(“F8:F10“).Copy Destination:=Sheets(“Sheet2”).Range(“A1”)
End Sub

Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q5 - What do your understand by variable?

  • Variable nick name we give to data or object.
  • It easy to call and we can use them multiple times in coding.
Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q6 - How many type of variable available in VBA?

There are two type of variable available in VBA.

  • Data
    • Nummeric data type
      1. Byte
      2. Interger
      3. Long
      4. Single
      5. Double
    • Text Data type
      1. String
      2. Date
      3. Boolean

 

  •  Object
    • Internal Object
      1. Workbook
      2. Worksheet
      3. Range
    • External Object
      1. Outlook
      2. FSO
      3. MS Access
      4. PowerPoint

 

Review below image for Type of Variable and its other information.

Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q7 - What is scope / life time of variable?

Life time of variable is divided in 3 type:-

  • Private or subroutine
  • Module
  • Public or Global
Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q8 - Difference between Inputbox and Msgbox?

There are 3 type of boxes in VBA.

  • InputBox
  • MsgBox
  • Application.Inputbox

InputBox :- It is used to take a input from user like Value, Path of any file etc

 

Msgbox :- It is used to show a message to user like path is not valid, incorrect value etc.

 

Application.InputBox :- It is used to pick the value from excel or select range during runtime.

Q9 - What is difference between event base coding and general coding?

There are 2 type of coding available in VBA

  • General coding
  • Event based coding

General Coding

  1. General coding always written on Module.

Event Based Coding

  1. Event based coding are written on Sheets, Workbook, Userform etc

Q10 - Difference between Data type and Object type variable?

There are 2 type of Variable present in VBA

  • Data Type
  • Object Type

Few basic difference are as follows :-

  • The value stored in cells are called Data and cells. range. worksheets etc are know as Object of Excel.
  • We can store value normally in with data type where we have to initialize object to store it.

Q11 - What is variant data type explain it?

below are some features of Variant variable.

  • A Variant is a default data type, if we dont declare variable it will variant type.
  • Variant is a special data type / object that can contain any kind of data /object in it.
  • It required 16 bytes Memory.

Q12 - What is difference between Option Implicit and Option Explicit?

Option Implicit :- 

  • It is a default option of writing a macros.
  • In Option explicit it is not mandatory to define variable.

Option Explicit :- 

  • Option explicit makes mandatory to define variable.

Q13 - Why it is mandatory to declare a Variable?

Below are advantage of declaring Variable while writing a VBA code.

  • Variable will store less memory.
  • Intellisence will start working.
  • Coding performance will improve.
  • We can store value once and can use any where.

Q14 - Difference between .Select and .Activate?

In .Select we can select multiple objects but in .Activate we have only one object.

Please review below example for more details.

Ex1 :- Working with Sheets

In below example first code will work while second will not work.

Sub Select_Sheets_method()

Worksheets(Array(“Sheet1”, “Sheet2”)).Select
Selection.Value = “ExcelVBATips”

End Sub

Sub Active_Sheets_method()

Worksheets(Array(“Sheet1”, “Sheet2”)).Activate
Selection.Value = “ExcelVBATips”

End Sub
In above 2 coding first will work fine where second will through an bug.

Ex2 :- Working with Range

In below example below will delete all cells while in second code only first cell will get deleted.

Sub Select_Range()

Range(“B5,B9,B14”).Select
Selection.Delete
End Sub

Sub Activate_Range()

Range(“B5,B9,B14”).Activate
ActiveCell.Delete
End Sub

In Above 2 coding 1 is for .Select method where other is for .Activate method.

  • In first example it will delete all three cells.
  • In second example it will only delete active cell.

Ex3 :- Working with Range

In first example it will supply values to all cells while in second example it will only supply to single cells.

Sub Select_Range()

Range(“B5,B9,B14”).Select
Selection.Value = “ExcelVBATips”
End Sub

Sub Activate_Range()

Range(“B5,B9,B14”).Activate
ActiveCell = “ExcelVBATips”
End Sub

Q15 - How to find last row of sheet?

There are multiple ways of finding last row of any sheets

Lst_Row = Cells.SpecialCells(xlCellTypeLastCell).Row

Lst_Row = Range(“A1048576”).End(xlUp).Row

Lst_Row = ActiveSheet.UsedRange.Rows.Row

Q16 - What is difference between Function and procedure?

Below are few basic difference in function and Procedure :-

  • Function always return a value, Procedure does not return a value.
  • Function works on data type, where procedure works on both data and objects.
  • We can call function from procedure, but procedure cant be called by function.
  • We can apply function in Excel worksheets where procedure can be applied on excel sheets.

Q17 - What is difference between Property and Method?

Q18 - Difference between displayalert and screenupdating?

Both option are used to improve performance of VBA coding.

Application.ScreenUpdating = False or True

  • It is used to stop unnecessary movement of screens.

 

Application.DisplayAlerts = False or True

  • While running the macro, it is used to remove unnecessary pop-ups.

Q19 - Difference between ActiveWorkbook and Thisworkbook?

Thisworkbook :- In Excel file in which we are writing code is called ‘Thisworkbook’.

ActiveWorkbook :- A Excel file which is at top or in front of all files is called ‘ActiveWorkbook’. Thisworkbook can also be a Activeworkbook.

Q20 - Difference between Range and Cells?

  • Cells(m,n) refer a single cell address, where Range can refer Single cell addres and Range(multi cells) address
  • Cells(m,n) is used for 2 dimension loop, Range is used in single dimension loops.

Q21 - What is object model diagram, explain object model diagram for pivot items?

Object model Diagram shows us the flow of an object to other object, it help referring object to object while coding.

Object model Diagram of Pivot Items  :-

  1. Application
  2. Workbook
  3. PivotChache
  4. PivotTable
  5. PivotField
  6. PivotItems

Q22 - What do you understand by IF statement in VBA?

IF is an statement, which works on logical condition, if logical condition is TRUE it follow true scenario if condition is FALSE it follows False scenario.

We can apply IF statement in 3 ways :-

First Way :-

If Logical_Test then

‘your statement here

End if
Second Way :-

If Logical_Test then

‘your TRUE statement here

Else

‘your FALSE statement here

End if
Third Way :-

If Logical_Test1 then

‘your TRUE statement here

ElseIf Logical_Test2 then

‘your TRUE statement here

ElseIf Logical_Test3 then

‘your TRUE statement here

ElseIf Logical_Testn then

‘your TRUE statement here

ELse

‘your FALSE statement here

End if

Q23 - Type of loop available in VBA?

There are 6 type of Loops in VBA.

  1. For Loop
    • For … Next
    • For Each … Next
  2. Do While Loop
    • Do While … Loop
    • Do … While Loop
  3. Do Until Loop
    • Do Until … Loop
    • Do … Until Loop

Q24 - Difference between Internal and External objects? with example?

The object which are inbuilt with in excel are called Internal object.

  • Range
  • Workbook
  • Worksheet
  • PivotChache
  • Charts etc.

The object which are not inbuilt with in excel, and are added through Tool are called External object.

  • FSO
  • OutLook
  • MS Access
  • Notepad
  • PowerPoint etc.

Q25 - Difference between Redim and ReDim Preserve?

ReDim and ReDim Preserve are related to Arrays in VBA. When initially we are not sure about size of any data then we define array as default and later on we redefine them.

Redim :- Removes all items stored in variable and store new values.

ReDim Preserve :- It Dosent remove existing stored values and it will store values for new positions.

Review VBA_Array for more details.
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q26 - Difference between Early binding and Late binding? which in better to use?

Early Binding :-

  1. When we refer any object from Tools –> References and start working on automating our EUC or tools on its properties or methods then it is called ‘Early Binding’.
  2. It gives intellisence to write our code easily.
  3. It works in current version of Excel, for other version of excel we have add references again.
  4. Adding more References can result in file large size.
  5. Your code will run faster as it has inbuilt methods and properties

Late Binding :-

  1. When we dont refer any object from Tools –> References and create our own object and start working on automating our EUC or tools on objects then it is called ‘Late Binding’.
  2. It doesn’t gives intellisence to write our code, we have to write our code by own.
  3. It works in any version of Excel.
  4. Dosen’t impact file size much.
  5. Your code will be little slow comparative to Early Binding.

Q27 - Difference between Chart and ChartObject?

Chart               :- When we are planing to create new chart through VBA we declare chart object as chart.

  • Dim cht_new As Chart

ChartObject :- When we are planing to modify existing chart through VBA we declare chart object as chartObject.

  • Dim cht_Existing As ChartObject

Q28 - Difference between Visual Basic and Visual Basic for Applications?

Visual Basic :-

  • It is a programming language developed by Microsoft for their operating system Windows.
  • The structure of VB is very simple and its execution is very simple.

Visual Basic for Applications :-

  • Its helps us automating Microsoft applications and provide customized soulutions.
  • It’s a programming language for Microsoft includes: – Excel, PowerPoint, WinWord, MS Access, Visio etc.
  • It enables you to automate the various process or activity which you do manually in excel like generating report preparing chart & graphs doing calculation etc.

Q29 - When we get error 'Compile Error :- Procedure too large', What its limit? what its solution?

We get this error when Procedure exceeds the number of line of code.

Solution :- Break your code into 2 or more code.

Q30 - Difference between Hidden and VeryHidden sheets?

Visible :-

Syntex :- Worksheets(“Sheet1”).Visible = xlVisible

 

Hidden :-

Syntex :- Worksheets(“Sheet1”).Visible = xlHidden

 

VeryHidden :-

Syntex :- Worksheets(“Sheet1”).Visible = xlVeryHidden

Review VBA_Array for more details. 
Please help me in making it better, Kindly share your feedback on 8319560164 or write us on ExcelVBATips@gmail.com

Q31 - How to call Public Procedure from any module?

While giving ‘Call’ keyword we can call any procedure to other procedure.

Sub Main_Coding

Call Procedure_Name

End Sub

Q32 - How to call Private Procedure from any module?

To call Private Macro from other module we use below code.

Application.Run “Macro_Name

Q33 - Difference between ByVal and ByRef?

Toggle content goes here, click edit button to change this text.

Q34 - How we can pass argument to function o procedure?

Toggle content goes here, click edit button to change this text.

Q35 - How many type of error you know, how we can use them?

Toggle content goes here, click edit button to change this text.

Q40 - Difference between Array, Dictionary and Collection?

Toggle content goes here, click edit button to change this text.

Q44 - Define ADO, ODBC, DAO, OLEDB, DLL?

Toggle content goes here, click edit button to change this text.

Q45 - What do you understand by FSO?

FSO stands for FileSystemObject, it is used to work on different file and folder in system.

  • Dim FSO as FileSystemObject

Q46 - How to copy and move file from one location to other?

Toggle content goes here, click edit button to change this text.

Q50 - How to improve performance of Excel file / VBA coding?

Toggle content goes here, click edit button to change this text.

Q51 - What is difference between CurrentRegion and UsedRange?

Toggle content goes here, click edit button to change this text.

Q55 - What do you understant by Dependency tree and how we can create it?

Toggle content goes here, click edit button to change this text.

Q56 - Difference between Module and Class Module?

Toggle content goes here, click edit button to change this text.

Q57 - Name few in build Module?

Toggle content goes here, click edit button to change this text.

Q58 - How to save a macro so it can be run / execute from any file?

Coming soon 🙂

ExcelVBATips is a group of professionals, Managed by Chetna Sharma. Please whatApp us 8285052214 for more information or email us on ExcelVBATips@gmail.com

Related topics

  • Generic Interview
  • Excel Interview
  • VBA Interview
  • Access Interview
  • SQL Interview
  • SAS Interview