If you have a question, please send it to us.
1) What is a Macro ?
A macro can be defined as lines of VBA code that can be run multiple times.
When you have a repetitive task you can use a macro to quickly repeat the steps.
2) Is there any Microsoft documentation for VBA Programming ?
link - docs.microsoft.com/en-gb/office/vba/api/overview/language-reference
3) Is there a forum for questions about VBA Programming ?
Post your questions in the Microsoft Q&A forum with the topic "office-vba-dev".
link - docs.microsoft.com/en-us/answers/topics/office-vba-dev.html
There is also a forum on techcommunity. The Excel hub forum with the label "Macros and VBA".
The following labels also exist but should not be used "Macros & VBA" and "VBA".
link - techcommunity.microsoft.com/t5/forums/filteredbylabelpage/board-id/ExcelGeneral/label-name/Macros%20and%20VBA
There is also a forum on Stack Overflow with the tag "vba".
link - stackoverflow.com/questions/tagged/vba
4) What is the Personal.xlsb ?
This is the personal macro workbook.
This is a special hidden workbook which you can use to store your macros.
It is open every time Excel is open assuming one has been created.
5) If a VBA macro is running and needs to be interrupted what keys would you press to interrupt it ?
(Ctrl + Break)
(Ctrl + Shift + Break)
6) If a VBA macro is running very slowly, what changes could you make to speed it up ?
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
Worksheet.EnableFormatConditionsCalculation = False
Read and write large blocks of cells in a single operation
Avoid selecting or activating any objects
Declare all variables with explicit data types
Never use the Variant data type
7) What is the difference between ThisWorkbook and ActiveWorkbook ?
Application.ActiveWorkbook 'workbook in the active window.
Application.ThisWorkbook 'workbook where the code is actually running.
8) Write code to display the values in cells B1 to B5 in the Immediate Window ?
Dim lrowno As Long
For lrowno = 1 To 5
Debug.Print Range("B" & lrowno).Value
9) What is the Application.Union method ?
Returns a range object that represents the union of two or more ranges.
Dim oRange1 As Excel.Range
Set oRange1 = Application.Union(Range("A1:A5"), Range("C1:C5"))
10) What is the Application.Intersect method ?
Returns a range that object that represents the rectangular intersection of two or more ranges.
Dim oRange2 As Excel.Range
Set oRange2 = Application.Intersect(Range("B1:B5"), Range("A3:E3"))
11) Write code to select all the non-blank cells
'the 23 = xlSpecialCellsValue.xlNumbers | xlTextValues | xlLogical | xlErrors
12) Can you give the name of 3 workbook level events ?
NewSheet - Occurs before an additional worksheet is added to this workbook.
Open - Occurs before the workbook is opened.
SheetCalculate - Occurs after the user re-calculates any worksheet in this workbook.
13) Can you give the name of 3 worksheet level events ?
BeforeDoubleClick - Occurs before any cell on the worksheet is double clicked.
Change - Occurs when any cell on the worksheet has its value changed.
SelectionChange - Occurs when the selection is changed on the worksheet.
14) Can you give the name of 3 application level events ?
WorkbookBeforeClose - Occurs before any open workbook is closed.
WindowActivate - Occurs when the user moves focus to another window or open workbook.
WorkbookModelChange - Occurs when a data model is updated.
15) Write code to test if a worksheet is a chart sheet ?
If (Sheets(1).Type = xlChart) Then
If (VBA.TypeName(Sheets(1)) = "Chart Sheet") Then
16) Write code to ask a question and put the answer in cell A1 ?
Range("A1").Value = Application.InputBox("What is your name?")
17) What are Form Controls and how are they different from ActiveX controls ?
Form controls were added in Excel 5.0. ActiveX controls were added in Excel 97.
Form controls are very basic and can be placed on charts. ActiveX controls are more complicated and cannot be placed on charts.
Both types of controls can be placed on worksheets.
Both types can link to cells and ranges on a worksheet.
18) Remove any code that selects or activates ranges or objects.
You do not actually have to select or activate something in order to change or manipulate it.
Avoid using any of these in your macros. The current selection might not always be what you are expecting.
19) Never use date literals
Dim dtMyDate As Date
dtMyDate = #02/03/2021#
These dates ignore the regional settings and are always interpreted in US date format (mm/dd/yyyy)
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext