Macros

Lakukan tugas rumah & ujian kamu dengan baik sekarang menggunakan Quizwiz!

Data types of a variable: (Dim Variable Name as Datatype)

1. Integer = whole #, NO decimals 2. Single = # with or without a decimal place 3. Currency = # with a dollar amount 4. String = text

A variable may be of several different data types:

1. Integer = whole number with no decimals 2. Single = number with or without decimals places 3. Currency = number with dollar sign 4.String = test Variable name can be anything

How is a private macro/procedure activated?

By built in events recognized by Excel -Open -Activate -Deactivate -BeforeClose -BeforePrint -BeforeSave

How can a new module be added?

By clicking on the Insert menu in VBE and then clicking on Module

When assigning short cuts, you should use....

Capital Letters

Microsoft Visual Basic Help

Seperate from normal Excel help feature. Must be in Visual Basic to access this help. Click Visual Basic for Applications Language Reference link

User Defined Functions

Special types of Visual Basic procedure, which returns a result Use to create custom functions to perform calculations in your macros or to use directly in your workbook

Titletext (optional) in a Message Box

Supplies a caption or title at the top of your message box (MUST be in quotation marks)

Where can modules be renamed?

The Properties Window which is accessed under the View menu in VBE

Object

an element of the Excel Application -ex. worksheet, a cell, a range of cells, or the entire workbook - macro code is written to manipulate an object by performing an action on that object

How do you write a new macro from the Project Explorer Window?

by inserting a new module or clicking on ThisWorkbook in the Project Explorer Window

Where are variables created and defined?

within the macro code. Once you create a variable and give it a value, you can assign the value to an object

What are the 3 main objectives of Macros?

1. Automate repetetive Excel task - improves efficiency 2. Automate and control input -improves efficiency and accuracy 3. Guide users through a spreadsheet - improves efficiency and accuracy

Define Variable

A named location in Visual Basic used for storing data during the execution of a macro -Define before using

Message Boxes

Allow us to display information in a simple dialog box. You can make one or more buttons available to user for closing the message box and responding to the message

If....Then....Else....End If

Allows Visual Basic to conditionally execute code, depending on a logical condition

Snytax "Dim variablename"

Creates a variable with no data type specified Takes up more room space because enough room is reserved for all data types

What does Dim stand for when declaring a variable?

Define in Memory

What should be the last line of code for a user-defined function?

End Function

T/F: User defined functions must be stored in ThisWorkbook in Visual Basic Editor

FALSE stored in module

Define Visual Basic Editor (VBE)

Is embedded within Excel and can be accessed through the Developer Tab. Macros can be created, edited, or deleted here.

Are spaces allowed in macro names?

NO

What are Macros referred to in in Visual Basic terminology?

Procedures

Visual Basic Commands

The steps of the macro that define the macro's functionality. We use objects, properties, methods, and variable to create Visual Basic Commands

Buttonoptions (optional) within a message box

The text for the buttons you want to have appear in your message box for the user to click on -default is an OK button

The Code Window

The window in Visual Basic Editor (VBE) that displays the Visual Basic code (commands) for a macro

What is the benefit of a Macro?

They allow us to simplify our use of computers and alleviate tedious computer tasks

Until Loops

Until: continues to loop UNTIL the condition becomes true (Will stop when condition becomes true)

How to use relative referencing when recording a Macro

Use relative referencing button under the Code section on the developer tab

What does vb stand for in a Message Box buttonoption?

Variable Buttons, don't use quotation marks for this

All Macros created in Excel are stored where?

Visual Basic Editor (VBE) on the Developer tab

Where are Macro instructions written?

Visual Basic which is a programming language

Different Buttonoptions

Want a Yes and a No button - vbYesNo

The else statements in an If..Then indicates what?

What should happen if the condition is false (if the condition is not met)

The statements in an If.. Then indicates what?

What should happen if the condition is true (if the condition is met)

How are comments indicated within Visual Basic Code (commands)?

With an apostrophe at the beginning of the comment and they are color-coded green

Messagetext within a message box

Within the message boxes -the message you want to send to the user""

Module

a collection of macros located in Visual Basic Editor. Several macros can be placed in one module or each maco can have it's own module

Variable

a named storage location in Visual Basic editor that contains data you can retrieve and modify while the macro code is being executed

Method

an action that can be performed on an object - ex. clearcontents, copy, delete, select, save, close

Property

an attribute of an objet in Excel that defines/changes one of its characteristics (name, size, color, formula, value, or location.)

Define debugging

correcting errors in a macro

Local Window

displays variables and how they change as a macro is run. Useful in debugging a macro. Accessed under the View menu in Visual Basic Editor

Comments

documentation located in Visual Basic code (commands) to help clarify the purpose and meaning of the macro. Provide info on the macro without affecting the actual execution of the macro.

Project Explorer Window

located on the left side of Visual Basic Editor. Within, you can access macros you have recorded or written in modules or ThisWorkbook. Can be accessed under the View menu in Visual Basic Editor

The general syntax for Visual Basic code involving methods

object.method(parameter value). - parameter values are optional - Ex. Range("A3").Select = method is select

General syntax for commands that change the properties of an object

object.property=expression - Range("A3").Font.ColorIndex=3 = property is Font.ColorIndex=3

What are used to create macro code (VB commands)?

objects, methods, properties and variables

All objects have what?

properties

Syntax

the set of rules specifying how you must enter Visual Basic commands. You must follow syntax precisely or the macro will not run

F1 function key

will retrieve help when pressed while an object, property, or method of Visual Basic code is highlighted in the Visual Basic Editor

What are the 3 methods for creating a Macro?

1. Use the Macro Recorder to record a new macro 2. Write a new macro in Visual Basic Editor 3. Copy, paste, and edit/customize a previously created macro

Macro

A series of commands, actions and functions that can be stored and initiated (run) whenever you want to perform a particular task. (a program)

What does a Macro provide?

A set of instructions to Excel that indicate which commands Excel should execute

The If.....Then.....Else.....End If code

Allows code to be conditionally executed depending on whether a specified logical condition has been met

Do....Loop

Allows us to repeat a series of Visual Basic statements (lines of code) UNTIL a specified condition is met or WHILE a specific condition is met

How to add the developer tab

File, Options, Customize Ribbon, Select check box for Developer

How to save/enable macros?

File, Save As, Save As type drop down to "Excel Macro-Enabled Workbook"

What are Visual Basic Commands also referred to as ?

Macro Code

While Loops

While: continues to loop WHILE the condition is true (Will stop when condition becomes false)

Define Visual Basic for Applications (VBA)

a programming language you can use to create macros. - a descendant of the BASIC programming lang. used in all Office products

Response in a Message Box

a variable location that has been defined in the macro code that will store the value of which button was selected by the user


Set pelajaran terkait

Senior Med Surg Prep U Ch 68: Mgmnt of Pt w/Neurologic Trauma

View Set

BSCI222 Exam 3 Textbook Problems (Topics 9-13) and gss practice problems

View Set

Introduction to SPC (Statistical Process Control)

View Set

HIPAA Compliance 04: Protected Health Information

View Set

Bio Practice Questions & answers

View Set