We can define variable in different levels:. Local Level: Variables which are defined with DIM statement in a procedure or functions. Module Level: Which are defined with DIM statement on top of a module, can be accessed in entire module. Global Level: Which are defined Public statement at top of any module, can be accessed in entire project.Read More Basic VBA Interview Questions and AnswersAdvanced VBA Interview Questions and AnswersHere are the top most Advanced VBA Questions and Answers covered from Advanced concepts of VBA Programming. Arguments can be passed in two ways in VBA Subroutines and Functions:ByVal: When an argument is passed By Value, the value assigned to the argument is passed to the procedure.
And any changes that are made to the argument inside a procedure, it will be lost when the procedure is ends.ByRef: When an argument is passed By Ref, the actual address assigned to the argument is passed to the procedure. And any changes that are made to the argument inside the procedure will be passed when the procedure ends.And By Ref is default in VBA. Workbook, Worksheet modules are Class modules.
These Excel VBA Interview questions are being posted keeping in mind that reader is aware of working with VBA, have some programming and MS Excel background and is aware of terminologies. This question bank is helpful for both Interviewee and Interviewer as it provides a quick channel of questions and answers covering major topics of Excel and VBA. The intent of MockQuestions.com is for our users to build confidence for their job interview, by using our thousands of interview questions and answers as they practice and prepare for their interview. We believe, most of our visitors can become more likely to succeed in their job interview with hard-work and practice.
Read More Advanced VBA Interview Questions and AnswersTop Most Excel VBA Interview Questions and AnswersWe have covered most frequently asked Excel VBA Interview Questions and Answers, divided into different sections. Take your own time to understand the questions and answers.
Please ask us if you have any further questions.Please let us know your interview experience and share your experience. Please let us know if you want to add any questions, which we have missed here. Please find the following steps to delete macros from the workbook.Step 1: Go To Developer tab from the main ribbon of Excel window.Step 2: Click on the Macros command button to see the available macros in the active workbook.Step 3: Once you click on the Macros command button, Macro dialog box will appear on the screen.Step 4: Select macro name which you want to delete macro and then click on ‘Delete’ command button.Step 5: Now, It will show the confirmation dialog box.
Click on Ok to delete the macro. This is one of the most commonly asked Excel VBA Interview Questions and Answers. You can use WorkbookOpen Event to run macros automatically in Excel VBA while opening Workbook.To get WorkbookOpen Event in Excel, please find the following steps.1. Go To VBA Editor.2.
Click on ‘ThisWorkbook’ from the Project Explorer.3. Now, you can see two drop down lists in the right side.4. Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.5. Now, you can see the following code. Private Sub WorkbookOpen‘Your Statements.End Sub6. You can add the code in-between the above lines to run a macro.7.
Save and close the workbook8. Now, reopen the workbook to test the macro.Example: Private Sub WorkbookOpenMsgBox 'Workbook has Opened Successfully.”, vbInformationEnd SubIn the above example, the macro will run automatically when we are opening workbook. Now, it will display message like “Workbook has Opened Successfully.”.Or we can also define a procedure named AutoOpen in any code module, this will execute while opening the macro file.
You can use WorkbookOpen Event to show UserForm automatically in Excel VBA when we open Workbook.To get WorkbookOpen Event in Excel, please find the following steps.1. Go To VBA Editior.2. Click on ‘ThisWorkbook’ from the Project Explorer.3. Now, you can see two drop down lists in the right side of the VBA Editor window.4. Select ‘Workbook’ from the first drop down list and then choose ‘Open’ from the second drop down list.5.
Now, you can see the following code. Private Sub WorkbookOpen‘Your Statements.End Sub6. You can add the code in-between the above lines to run a macro.7. Save and close the workbook8. Now, reopen the workbook to test the macro.Example: Private Sub WorkbookOpen‘ Here 'MyForm” is the UserForm name.MyForm.ShowEnd SubIn the above example, the macro will show the UserForm(Named ‘MyForm’) automatically when we open Workbook.Note: Before running above macro add UserForm and then assign the name of the UserForm to ‘MyForm’.
ByVal vs ByRef in VBA is also one of the most frequently asked Excel VBA Interview Questions and Answers.ByVal:Specifies that an argument is passed in such a way that the called procedure or property cannot change the value of a variable underlying the argument in the calling code.ByRef:Specifies that an argument is passed in such a way that the called procedure can change the value of a variable underlying the argument in the calling code.Note: Default value is ByRef. It is good practice to include the ByRef declaration if you are going to change the value of the parameter. Please find the following steps to add UserForm or Module or Class Module to the VBA Project.Add UserForm:Step 1: Go To Insert menu in the VBA Editor window.Step 2: Click on ‘UserForm to add to the Project. Now you can see added UserForm in the Project Explorer.
Default UserForm name will be ‘UserForm1’. You can change the UserForm name with using propertiesAdd Module:Step 1: Go To Insert menu in the VBA Editor window.Step 2: Click on ‘Module’ to add to the Project. Now you can see added Module in the Project Explorer. Default module name will be ‘Module1’. You can change the module name with using properties.Add Class Module:Step 1: Go To Insert menu in the VBA Editor window.Step 2: Click on ‘Class Module’ to add to the Project. Now you can see added Class Module in the Project Explorer.
Default Class module name will be ‘Class1’. You can change the class module name with using properties. Data Type: A data type tells, what kind of variable we are going to use in a procedure or function. The information that specifies how much space a variable needs called a data type.Before using variable, we need to know how much space the variable will occupy in memory, because different variables occupy different amount of space in memory.We can declare the variable in the following way.Dim VariableName as DatatypeExample:Dim iCnt as IntegerWhere iCnt represents VariableName and Integer represents Datatype. Please find the following steps to assign macro to a button.Step 1: Go to the Developer tab from the excel ribbon menu, go to Forms Control group.Step 2: Click on Button from the Form Controls.Step 3: Click the worksheet location where you want the button to appear.Step 4: Drag the button in the sheet.Step 5: Right click on the button, click on Assign Macro.Step 6: Assign Macro Dialog box will appear now, click the name of the macro that you want to assign to the button.
Click on OK.Step 7: You can format the control by specifying control properties.Step 8: Click on button to test. Now, your macro should run. Understanding the scope of variables is very important for VBA Developers, it is also one of the most frequently asked Excel VBA Interview Questions and Answers.When we are working with variables, it is important to understand the Scope of a Variable.
The Scope describes the accessibility or life time or visibility of a variable.There are four levels of Scope:. Procedure-Level Scope.
Module-Level Scope. Project-Level Scope. Global-Level ScopeFor more information please find the following link. We have several best practices to follow while coding VBA. This is also one of the most frequently asked Excel VBA Interview Questions and Answers.
This helps interviewer to understand your real time experience in VBA.We can fasten the execution of macros or VBA Procedures by following the below tips.1. Declare the variables and avoid using ‘Variant’ Data Type.2. Turn Off Screen Updating3. Turn Off Automatic Calculations4.
Disable Events5. Use With Statement6. Use vbNullString instead of “”.7. Release memory objects at the end of the procedure.
Enabling and Disabling the Screen updating will be used in almost all projects. Questions on understanding the screen updating are also one of the most frequently asked Excel VBA Interview Questions and Answers.Here is the approach to enable or disable screen updating or screen flickering.In order to stop the screen flickering, stop the screen updating at Staring of the procedure:Application.ScreenUpdating = FalseYou have to set back screen updating as True Before ending of the procedure:Application.ScreenUpdating = True. You can find a specific file exist or not in the following two ways.1. Using FileSystemObject:Here is the example to check file exist or not using ‘FileSystemObject’.
Sub CheckFile2Dim FSODim sFileName As StringsFileName = 'C:/Test/Workbook.xls'Set FSO = CreateObject('Scripting.FileSystemObject')If Not FSO.FileExists(sFileName) ThenMsgBox 'File Does Not Exists.' ElseMsgBox 'File Exists.' End IfEnd Sub2. Using Dir Function:Here is the example to check file exist or not using ‘Dir’ function.Sub CheckFile1Dim sFileName As StringsFileName = 'C:/Test/Workbook.xls'If Dir(sFileName) ' ThenMsgBox 'File Exists.' ElseMsgBox 'File Does Not Exists.' End IfEnd SubFor more information please find the following link.
Please find the following statements to delete a file from the specified location. Sub sbDeleteFileDim FSODim sFile As StringsFile = 'C:Test.xlsm'Set ObjectSet FSO = CreateObject('Scripting.FileSystemObject')'Check File Exists or NotIf FSO.FileExists(sFile) ThenFSO.DeleteFile sFile, TrueMsgBox 'Deleted The File Successfully', vbInformation, 'Done!' ElseMsgBox 'Specified File Not Found', vbInformation, 'Not Found!' End IfEnd SubNote: Before deleting file from the specified location, we have to check whether file is exists or not in the specified location. In the above example we are using statement:’ FSO.FileExists(sFile)’ to check for the file.Please find the following link for more information on deleting a file in a specified location. You can move a file from one location to another location in the following way. Sub MoveFileDim sFileName As StringDim dFileName As StringsFileName = 'D:Test.xlsx' 'Source File Location NamedFileName = 'E:Test.xlsx' 'Destination File Location NameName sFileName As sFileNameEnd SubRead More MS Excel VBA Interview Questions and AnswersMS Access VBA Interview Questions and Answers with ExamplesHere are the most commonly asked MS Access VBA Questions and Answers covered from Basics of VBA Programming.
Here is the following VBA procedure to delete records in the access database table using VBA.' Delete records in the access database table using VBASub sbDeleteRecordsTableDim DBase As DatabaseDim strSQL As String'Open DatabseSet DBase = OpenDatabase('C:UsersPNRaoDocumentsMyDatabase.accdb')strSQL = 'Delete from MyTable where ENumber=12345;' Deleting records the tableDoCmd.RunSQL strSQLDBase.CloseEnd Sub10+ MS Access VBA Interview Questions and AnswersMS PowerPoint VBA Interview Questions and Answers with ExamplesHere are the most commonly asked MS PowerPoint VBA Questions and Answers covered from Basics of VBA Programming. Here is the following macro to delete 1st slide in a PowerPoint Presentation.Example 1:Sub DeleteSlidePresentationEx1ActivePresentation.Slides(1).DeleteEnd SubExample 2:'Delete 3rd slide in a presentationSub DeleteSlidePresentationEx2ActivePresentation.Slides(3).DeleteEnd SubRead More MS Powerpoint VBA Interview Questions and AnswersMS Word VBA Interview Questions and Answers with ExamplesHere are the most commonly asked MS Word VBA Questions and Answers covered from Basics of VBA Programming. Here is the following VBA procedure to add attachment and send a message using Outlook.'
Add attachment and send a message using OutlookSub SendMessageWithAttachment'Variable DeclarationDim oMessage As Outlook.MailItemDim oAttachment As Outlook.Attachments'Create ObjectsSet oMessage = Application.CreateItem(olMailItem)Set oAttachment = oMessage.Attachments'Add attachmentoAttachment.Add 'D:/Test.doc', olByValue, 1, 'Test'Add message detailsWith oMessage.To = '[email protected]'.Subject = 'Test Email'.Body = 'This is a test message.' .SendEnd WithEnd Sub. Here is the following VBA procedure to create a Task in outlook.Create a TaskSub CreateTask'Variable DeclarationDim Task As TaskItem'Create objectSet myTask = Application.CreateItem(ItemType:=olTaskItem)End SubRead More MS Outlook VBA Interview Questions and AnswersVBA Interview Questions and Answers PDF – Free Download pdf fileYou can download the free VBA Interview Questions and Answers PDF with Examples Explained.Simple and Basic VBA Interview Questions and Answers for Beginners with ExamplesBelow are the 15+ Basic VBA Questions with examples for VBA beginners. Please refer these example VBA Basic Examples if you are a fresher or just beginner. This will be helpful to answer the basic VBA Questions.Advanced VBA Interview Questions and Answers for ExperiencedBelow are the 100+ examples on Advanced VBA Problems with solutions. Please refer these Advanced VBA Examples if you are attending for a experienced job/position profile requirement. Hi all,I have a userform on a worksheet.
Now, I open anothe workbook, the userform still stays on top of all the workbooks. However, if I open a new workbook(new instance of excel), then it does not show. But that would help me for nothing.Can anybody help me in this. I need the userform to be a part of the excel which I open.
Not for all workbooks.Few comments before I complete: This was working as intended. All of a sudden, I messed up with the code and it burnt out.Secondly, the ShowModel of userform is set to False as I need to hide the workbook when userform opens. Hence, I cannot set that to true.Any suggestions are welcome. Please help.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |