Hands on 1.1 Setting up macro development Flashcards
Setting up trusted location
Developer => Macro Security => Trusted locations => add New Location
Using relative or absolute reference in macro
By default macro recording uses absolute,
View tab => Macros=>Use relative references
if you record a macro in cell A1 which moves the cursor A3 with this option turned on, running the resulting macrp in cell J6 would move the cursor the cursor J8
Applying Custom Formatting to the Worksheet
Home | Editing | Find & Select | Go To Special
this selects all cells with texts or numbers etc, then apply same format for all
shortcut for Running Macro
from VBE press F5, from the excel worksheet Alt+F8
Assigning Macro to a Keyboard Shortcut
Alt+F8 => click the Macro=>Options=> now assign whatever
avoid conflicts of shortcuts
Running the Macro from the Quick Access Toolbar, ie creating a button
Customize the Quick Access Toolbar =>
More Commands=>
commands from drop-down list box, select Macros=>
Customize Quick Access Toolbar drop-down list box, select what file=>
and then add
Running a Macro from a Button Placed on a Worksheet
Developer => Form Controls, Insert => Select button image
Recording a Macro in the Personal Macro Workbook
This is where you pull out your created macro and use it in some other workbooks wither through with your shortcuts or button
explain VBE windows
Project explorer Ctrl+R, Properties F4, Code window F7
How to rename default project name VBAProject to own choice
Select it in Project explorer and in properties double click the name then type your own choice
how to sign a
VBA project with a self-issued digital certificate and how to protect your macros from
others by locking a VBA project.
A digital signature confirms that the programming code comes from a specific author and that it has not been tampered with or altered in any way since the VBA project was signed.
If you
simply want to avoid the security warning message when running your own macros, you can create a digital certificate yourself. This certificate will only be valid on the machine where it was created.
SELFCERT.exe find it and create a cert there.
in Project explorer right click the VBA project and go to properties and Protection. this will create a password and you have signed macro
Although you don’t need to lock the project from yourself, this steps demonstrate how you can prevent others from viewing and altering your
code.
Objects, Collections, Properties, Methods
Properties are great. They let you change the look of the object, but how can you control the actions? Before you can make Excel carry out some tasks, you need to
know another term. Objects have methods. Each action you want the object to perform is called a method. The most important Visual Basic method is the Add method, which you can use to add a new workbook or worksheet. Objects can use various methods. For example, the Range object has special methods that allow you to clear the cell contents (ClearContents method), formats (ClearFormats method), and both contents and formats (Clear method). Other methods allow objects to be selected, copied, or moved.
Rule #1: Referring to the property of an object
Object.object.property
Worksheets(“Sheet1”).Range(“A4”).Value
Rule #2: Changing the property of an object
Object.Property = Value
Value can be number
text (has to have “..”) and a logical value (True or False).
Rule #3: Returning the current value of the object property
Variable = Object.Property
for instance
CellValue=Range(“A4”).Value
This instruction saves the current value of cell A4 in the variable named CellValue.
Rule #4: Referring to the object’s method
Object.Method
Range(“A4”).ClearContents
If the method requires arguments, the syntax is as follows:
Object.Method (argument1, argument2, … argumentN)
How you break up a long VBA statement into two or more lines
Selection.PasteSpecial _
Paste:=xlValues, _
Operation:=xlMultiply, _
SkipBlanks: =False, _
Transpose:=False
You cannot use the line continuation character between a colon and an equals sign.
What do you do have Visual Basic automatically check for correct syntax after you enter a line of code,
choose Tools | Options in the Visual Basic Editor window. Make sure the Auto Syntax Check setting is checked on the Editor tab
syntax errors: Rum time
what is ?
PS:the other syntax error is logic
Runtime errors are often caused by those unexpected situations that the programmer did not think of while writing the code. These occur, for example, when the program is trying to access a drive or a file that does not exist on a user’s
computer, or copy a file to a CD-ROM or other media without first checking whether the media is available.
syntax errors: Logic error
what is ?
The logic error often does not generate a specific error message. The procedure may have no flaws in its syntax and may even run without errors, yet it produces incorrect results. Logic errors are usually very difficult to locate, and those that happen intermittently are so well concealed that you can count on spending long hours, and even days, trying to locate the source of the error.
how to turn on auto list Properties/Methods
Tools | Options. In the Options dialog box, click the Editor tab, and make sure the Auto List Members check box is selected.
Visual Basic methods may require one or more arguments to see this Parameter Info tooltip
enter the method or function name, follow it with the left arenthesis, and press Ctrl+Shift+I.
You can also click the Parameter Info button on the Edit toolbar or choose Edit |
Parameter Info.
Using Object Browser to Locate VBA Procedures
in VBE press F2 then from drop down menu select Personal
LEARNING ABOUT OBJECTS
what are the steps if you face problems in VBA
1) When in doubt about objects, properties, or methods in an existing VBA procedure, fire up the online help by pressing F1.
2) If you need a quick listing of properties and methods for every available object or have trouble locating a hard-to-find procedure, go with the Object Browser.
3) If you want to experiment with VBA and see the results of the VBA commands immediately, activate the Immediate window.
