Hands on 1.1 Setting up macro development

Question 1

Setting up trusted location

Answer 1

Developer => Macro Security => Trusted locations => add New Location

Question 2

Using relative or absolute reference in macro

Answer 2

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

Question 3

Applying Custom Formatting to the Worksheet

Answer 3

Home | Editing | Find & Select | Go To Special

this selects all cells with texts or numbers etc, then apply same format for all

Question 4

shortcut for Running Macro

Answer 4

from VBE press F5, from the excel worksheet Alt+F8

Question 5

Assigning Macro to a Keyboard Shortcut

Answer 5

Alt+F8 => click the Macro=>Options=> now assign whatever

avoid conflicts of shortcuts

Question 6

Running the Macro from the Quick Access Toolbar, ie creating a button

Answer 6

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

Question 7

Running a Macro from a Button Placed on a Worksheet

Answer 7

Developer => Form Controls, Insert => Select button image

Question 8

Recording a Macro in the Personal Macro Workbook

Answer 8

This is where you pull out your created macro and use it in some other workbooks wither through with your shortcuts or button

Question 9

explain VBE windows

Answer 9

Project explorer Ctrl+R, Properties F4, Code window F7

Question 10

How to rename default project name VBAProject to own choice

Answer 10

Select it in Project explorer and in properties double click the name then type your own choice

Question 11

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.

Answer 11

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.

Question 12

Objects, Collections, Properties, Methods

Answer 12

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.

Question 13

Rule #1: Referring to the property of an object

Answer 13

Object.object.property

Worksheets(“Sheet1”).Range(“A4”).Value

Question 14

Rule #2: Changing the property of an object

Answer 14

Object.Property = Value

Value can be number
text (has to have “..”) and a logical value (True or False).

Question 15

Rule #3: Returning the current value of the object property

Answer 15

Variable = Object.Property

for instance
CellValue=Range(“A4”).Value
This instruction saves the current value of cell A4 in the variable named CellValue.

Question 16

Rule #4: Referring to the object’s method

Answer 16

Object.Method
Range(“A4”).ClearContents

If the method requires arguments, the syntax is as follows:
Object.Method (argument1, argument2, … argumentN)

Question 17

How you break up a long VBA statement into two or more lines

Answer 17

Selection.PasteSpecial _
      Paste:=xlValues, _
      Operation:=xlMultiply, _
      SkipBlanks: =False, _
      Transpose:=False

You cannot use the line continuation character between a colon and an equals sign.

Question 18

What do you do have Visual Basic automatically check for correct syntax after you enter a line of code,

Answer 18

choose Tools | Options in the Visual Basic Editor window. Make sure the Auto Syntax Check setting is checked on the Editor tab

Question 19

syntax errors: Rum time
what is ?
PS:the other syntax error is logic

Answer 19

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.

Question 20

syntax errors: Logic error

what is ?

Answer 20

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.

Question 21

how to turn on auto list Properties/Methods

Answer 21

Tools | Options. In the Options dialog box, click the Editor tab, and make sure the Auto List Members check box is selected.

Question 22

Visual Basic methods may require one or more arguments to see this Parameter Info tooltip

Answer 22

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.

Question 23

Using Object Browser to Locate VBA Procedures

Answer 23

in VBE press F2 then from drop down menu select Personal

Question 24

LEARNING ABOUT OBJECTS

what are the steps if you face problems in VBA

Answer 24

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.

Question 25

how to select cells, how to enter data in cells, how to assign range names, how to format cells, and how to move, copy, and delete cells

Answer 25

There are three properties that allow you to access the Range object: the Range property, the Cells property, and the Offset property

Using the Range Property: 
    Range("A5").Select
    Range("A6:A18 , B3, C3").Select is selecting non adj cells
Using the Cells Property: 
    Cells(5,1).Select
    Range(Cells(6,1),Cells(10,1)).Select ie Range("A6:A10").Sel
    Cells.Select is selcting all cells in a workshseet
    Cells(16384).Select is Cell XFD
    Cells(16385).Select is Cell A2
Using the Offset Property
    Range("A1").Offset(1, 3).Select
    Range("D15").Offset(-2, -1).Select
    ActiveCell.Offset(-1, 0).Select

Question 26

Select the first,last cell in a row or column

Answer 26

ActiveCell.End(xltoLeft).Select
ActiveCell.End(cltoRight).Select
ActiveCell.End(xltoUp).Select
ActiveCell.End(xltoDown).Select

Question 27

Selecting Entire Rows and Columns

Answer 27

Selection.EntireRow.Select

Selection.EntireColumn.Select

Question 28

Moving, Copying, and Deleting Cells

Answer 28

Range(“A1”).Cut
Destination: = Range(“A4”)

Range(“A1”).Copy
Destination: = Range(“A4”)

Range(“A1”).Clear

ClearContents—Clears only data from a cell or range of cells
ClearFormats—Clears only applied formats
ClearComments—Clears all cell comments from the specified range
ClearNotes—Clears notes and sound notes from all the cells in the specified range
ClearHyperlinks—Removes all hyperlinks from the pecified range
ClearOutline—Clears the outline for the specified range