[PDF] [PDF] Intro to Excel VBA Macro Programming - Columbia University

[PDF] Preview Excel Macros Tutorial (PDF Version) - Tutorialspoint

Excel Macros i About the Tutorial An Excel macro is an action or a set of actions that you can record, give a name, save and run as many times as you want and 

[PDF] Intro to Excel VBA Macro Programming - Columbia University

Learn Excel tools by utilizing them in various cases Tools and Learning Slides[ pdf ] formulas are the backbone for VBA programming foundation

[PDF] Le Tableur EXCEL La Programmation en VBA - AgroParisTech

Ecrire l'instruction qui permet de faire référence à la cellule B2 dans la deuxième feuille de calcul de nom FEUIL2 du classeur TEST-MACRO XLS Page 21 Excel  

[PDF] Excel - Programmation VBA

Type de la valeur retournée par la fonction Un classeur Excel contenant du code VBA doit être enregistré au format XLSM, prenant en charge les macros Sinon 

[PDF] Getting Started with Excel Macros - Wiley India

Even casual users can sim- ply turn on Excel's macro recorder Recording a macro is like programming a phone number into your cell phone You first manually

[PDF] Excel Function Macro Basics

Excel 2016 Function Macro Basics Microsoft Excel© A function macro is the answer when calculations must be making function macros using Visual Basic

[PDF] 20 USEFUL EXCEL MACRO EXAMPLES - Trump Excel

In this Ebook, I am going to list some useful Excel macro examples that I need often and have Save Each Worksheet as a Separate PDF learn how to get it

[PDF] Macros in Excel: Recording, Running, and Editing

Note for Office 2007 users: In Excel 2007, macro tools are found on the Developer tab in the Ribbon If the Developer tab is not displayed, 1 Click the File Button

[PDF] Excel macro tutorial pdf - Squarespace

Change the font color of the active cell to blue This Excel Macro Tutorial for Beginners is accompanied by an Excel workbook containing the data and macros I use 

[PDF] excel multiple variables

[PDF] excel practical questions

[PDF] excel practical questions for students

[PDF] excel practice exercises with answers

[PDF] excel practice test exercises

[PDF] excel questions and answers for interview

[PDF] excel questions and answers template

[PDF] excel segmentation analysis

[PDF] excel shortcuts cheat sheet

[PDF] excel shortcuts for mac pdf

[PDF] excel shortcuts pdf 2020

[PDF] excel statistical modeling

[PDF] excel t test p value

[PDF] excel test interview questions

[PDF] excel training level 1 pdf

Fall 2012 Workshop

George Zhao

gz2165@columbia.edu

Goals of the Workshop

yLearn Excel tools by utilizing them in various cases yTools and materials covered here are merely a sample of

Excel functionality

yUnderstand the logic and syntax behind Visual Basic programming, which interacts with the Excel interface yNo programming background required yCreate dynamic algorithms to approach cases yWhen data is changed, but retains its original format, the algorithm should be able to automatically handle the transition appropriately

Created by George Zhao2

Workshop Structure

yInstead of providing function and programming syntax to memorize, this workshop emphasizes case studies, through which the skills are utilized yCases: applicable situational tasks yTutorials: supplemental teaching material to understand foundational materials yBest recommended to follow along and do the exercises with accompanying spreadsheets

Created by George Zhao3

Workshop Resources

Lesson materials:

yLearning Slides[.pdf] yExercises -Blank [.xlsx] yExercises -Filled [.xlsm]

Created by George Zhao4

Contents Overview

yCase 1: Multiplication Table yCase 2: Percentile Calculations yTutorial 1: Variables and Arrays yCase 3: Hello World yTutorial 2: Functions yTutorial 3: Loops and Decisions yCase 4: Gradebook Tallying yCase 5: Loop through Data yTutorial 4: Recording Macro yCase 6: Select, Pull, Display yTutorial 5: Userform yCase 7: Subway Data All-Around Analysis

Created by George Zhao5

Multiplication Table

yTask: If given the following on an Excel worksheet, how do we fill this up? 12345
1 2 3 4 5

Created by George Zhao7

First Task: Building the Table

ySuppose we are only given one side of the table initially: yWe need 1, 2, 3, 4, 5 to be filled up on the top row, beginning in cell B1, going rightward yWe also want both sets of numbers to be bolded

Created by George Zhao8

Shift + Control + Arrow

yBegin by selecting cell B2 yShift + Control + Down arrow to select all elements until an empty cell (or the end of capacity limit of the worksheet) is reached yShift + Control + (Up / Down / Left / Right) arrow all work similarly yControl + B for bold yControl + C for copy

Created by George Zhao9

Paste Special with Shortcut

yUse arrow to move to cell B1 yCan Right Click > Paste Special yOr simply Alt + E + S ySelect Transpose (E)

Created by George Zhao10

Where to Start

yUse fixed reference cells 12345
1 2 3 4 5

Created by George Zhao11

1-Dimensional Fixed Reference

yExample: y = (1+r)*x^2, given a fixed value r r = 0.1 xy -39.9 -2 -1 0 1 2 3

Created by George Zhao12

Show Formulas

yFormulas > Show Formulas yToggle on and off between showing / not showing

Created by George Zhao13

Error: No Fixing Reference

yError r = 0.1 xy -39.9 -24 -1#VALUE! 00 15

2#VALUE!

39

Created by George Zhao14

1-Dimensional Fixed Reference

yFix cell reference in cells A1, B2, BB32: $A$1, $B$2, $BB$32 r = 0.1 xy -39.9 -24.4 -11.1 00 11.1 24.4
39.9

Created by George Zhao15

Deeper Look into Fixing Reference

yA1: not fixing column A nor row 1 y$A$1: fixing column A and row 1 y$A1: fixing ONLY column A, not row 1 yA$1: fixing ONLY row 1, not column A yWhatever (row and/or column) right after the $ sign is fixed yIf fixing BOTH row and column, press F4 while cursor is over the reference in the formula editing

Created by George Zhao16

Algorithm: Multiplication Table

yMultiply the row index by the column index yDo this for each cell in the row, and then for each row 12345
1 2 312
4 5

Created by George Zhao17

Example: Multiplication Table

yFocus again on cell E4: (12 = 3 x 4) yAll entries on row 4: product of 3 (A4) and ___ yAll entries on column E: product of 4 (E1) and ___

Created by George Zhao18

Think About It

yFocus on any single row: yWe are traversing through various columns, but want to fix the first term (A4), so fix the column letter (A) yFocus on any single column: yWe are traversing through various rows, but want to fix the second term (E1), so fix the row number (1)

Created by George Zhao19

Result of Fixing

yFix the column (A) from the first reference, and the row (1) from the second reference yF2 to illustrate the formula and the references (colored)

Created by George Zhao20

Shortcuts to Paste Formula

yDouble click on the square at the lower-right corner of the cell yThis pastes the formula down the column, avoids the effort of dragging the formula down across rows

Created by George Zhao21

Shortcuts to Paste Formula

columns yHave to manually drag it across the columns

Created by George Zhao22

Performance Evaluation

yHow would performance have been if we were dealing with a 50 x 50 table instead? yShift + Control + Down, copy, paste special (transpose) occurs constant time yDouble clicking to paste to formulas down occurs constant time yManually dragging the formulas across columns depends linearly on number of columns

Created by George Zhao23

Alt + Enter Pasting Method

yPaste the formula on one corner yPaste the formula on the other corner ySelect all of the cells in-between to apply the same formula, using Shift + Control + arrow to get all of the wanted data yClick Alt + Enter yMuch more useful when dealing with 1-dimensional list than 2-dimensional table

Created by George Zhao24

Alt + Enter Illustrated

yCopy the formula in cell B2, paste into F6, select everything in-between, and hit Alt + Enter yDifficult to capture the desired region efficiently with the Shift + Control + arrow method

Created by George Zhao25

Quick Way to Paste Formula

12345

112345

2246810

33691215

448121620

5510152025

Created by George Zhao26

Remarks

yWhen fixing cell reference, think: yAre we fixing the column? yAre we fixing the row? yBoth? yNeither? yAlt + Enter way to paste formulas is more useful in 1- dimensional situations

Created by George Zhao27

Pseudocode: The Approach

yMultiply the row index by the column index yDo this for each cell in the row, and then for each row 12345
1 2 312
4 5

Created by George Zhao28

Actual Code Juxtaposed

yMultiply the row index by the column index yDo this for each cell in the row, and then for each row

Created by George Zhao29

Task 1

yWe are given 20 x 10 matrix of all random numbers yUpon supplying various integer values between 0 and

100, denoted n, give the n-th percentile of each column

Created by George Zhao31

=PERCENTILE() y=PERCENTILE([array], k) yLet k be within [0, 1] y=PERCENTILE(A1:A10, .75) gives the 75thpercentile value of the data from A1 to A10 y=PERCENTILE(B1:B10, .05) gives the 5thpercentile value of the data from B1 to B10

Created by George Zhao32

Right Formula?

Created by George Zhao33

Need to Fix Reference

yB1:B20 Ȃfix the row? Column? Both? Neither? yA23/100 Ȃfix the row? Column? Both? Neither?

Created by George Zhao34

B1:B20

yStores the column of data points to be analyzed yThink of what happens when the formula is dragged on to adjacent cells yDO NOT want to shift down to B2:B21 and so forth Ȃ fix the row references yBut DO want shift right to C1:C20 Ȃdo not fix the column references yB$1:B$20

Created by George Zhao35

A23/100

yStores the k value yThink of what happens when the formula is dragged on to adjacent cells yDO want to shift down to A24 Ȃdo not fix row references yDO NOT want to shift right to B23 Ȃfix the column references y$A23/100

Created by George Zhao36

Refined Formula

Created by George Zhao37

Dynamic Formulas

yResults updates automatically for different k values

Created by George Zhao38

Task 2

yGiven several integers, called x, calculate the percentile rank of those integers yWhat percentile would these integers fit into? yIf x is out of the range, error would return y=PERCENTRANK([array], x)

Created by George Zhao39

Without Error Trapping

ySimilar cell reference fixing as previous y#N/A errors whenever: yx is smaller than the minimum value in the set yx is larger than the maximum value in the set

Created by George Zhao40

=IF() yReturns different values given the certainty of a condition y=IF([condition],[value if true],[value if false] yϋȋ͝ϋ͝ǡ̾E3̾ǡ̾BC̾Ȍ "‡-—"• DzE3dz yϋȋ͝ϋ͞ǡ̾E3̾ǡ̾BC̾Ȍ "‡-—"• DzBCdz y3—""‘•‡ ...‡ŽŽ A͝ ...‘-ƒ‹• Dz͞͠dz ySuppose A2 wants to show the value in A1, but only the value is divisible by 11, otherwise leave blank y=IF(MOD(A1,11)=0,A1,"")

Created by George Zhao41

Nested =IF()

y3—""‘•‡ ...‡ŽŽ A͝ ...‘-ƒ‹• Dz͞͠dz yIn cell A2, type =IF(MOD(A1,2)=0,IF(MOD(A1,3)=0,"DIV

BY 6","DIV BY 2"),"NOT DIV BY 2")

yIf divisible by 2: yIf not divisible by 2: yChange the value in A1 and see the result

Created by George Zhao42

=IFERROR() y=IFERROR([normal value], [value if error]) yFor B23 cell for example, we want =IFERROR(PERCENTRANK(B$1:B$20,$A32),"Out of

Range")

yReturn PERCENTRANK(B$1:B$20,$A32) to B23 cell, but

‹ˆ -Šƒ- "‡•—Ž-• ‹ ƒ ‡""‘"ǡ "‡-—" DzC—- ‘ˆ 2ƒ‰‡dz

instead

Created by George Zhao43

=IFERROR() vs. =IF() yLogically, =IFERROR(PERCENTRANK(B$1:B$20,$A32),"Out of

Range") is essentially this:

=IF(PERCENTRANK(B$1:B$20,$A32)=#N/A,"Out of

Range")

yPERCENTRANK(B$1:B$20,$A32) immediately throws ySo =IFERROR() is the only way to trap that error

Created by George Zhao44

=IFERROR() vs. =IF() y=IFERROR() is also much cleaner ySuppose in cell row 39, we want to display sum of rows

1 through 20

instead of the actual sum yIn B23 cell: =IF(SUM(B1:B20)<1000,"< 1000",SUM(B1:B20))

Created by George Zhao45

=SUMIF() yIn the previous example, output changed depending on the final output yIf the total final out is less than 1000, display the string yWhat if we want conditions for each entry? yIn row 40, sum the entries of rows of 1 to 20, but only each individual entry is greater than 70

Created by George Zhao46

Criteria

yBoolean condition within quotation marks yExamples: y‡•• -Šƒ ͜͜͝ǣ Dzώ͜͜͝dz y“—ƒŽ• -‘ ͜͜͝ǣ Dzϋ͜͜͝dz y y

y‡•• -Šƒ ‘" ‡“—ƒŽ -‘ ͜͜͝ǣ Dzώϋ͜͜͝dz

y=SUMIF(B1:B20, ">70") in this scenario

Created by George Zhao47

A Note on This Exercise

yNo Visual Basic programming was needed for this exercise yMost times, if a dynamic formula in a cell can give us all the information we need, use them instead of programs yFaster, easier debugging yMuch of the functions and algorithms done with formulas are the backbone for VBA programming foundation

Created by George Zhao48

yData yData yData yData yData

Created by George Zhao50

Let's Get Started͗ Variables

yDifferent types of data. Consider: yName of city: New York (words) yLength of 14thSt: 2.00 (decimal) yPopulation: 8,000,000 (integer) yMetroCard fare: 2.25 (currency) yVariables are categorized by the type of data they hold. yThere are basic types, and there can be user-defined.

Created by George Zhao51

Some Basic Variables

yConsider a course: yClass size: 30 (integer) yClass location: Hamilton Hall (string) yPass/Fail allowed: false (boolean) yAverage grade: 94.4 (double) yAverage letter grade: A (character)

Created by George Zhao52

Declaring Variables

yDim size as Integer yDim location as String yDim passFail as Boolean yDim avgGrade as Double yDim ltrGrade as Char yDim: denote local variables (existence in the running of procedure) yVariable Names: begin with letter, only contain letter, number, or underscore, cannot be reserved word

Created by George Zhao53

Initializing Variables

yCan do all the declaration in one line as follows: yDim size as Integer, location as String, passFail as

Boolean, avgGrade as Double, ltrGrade as Char

ysize = 30 ylocation = "Hamilton Hall" ypassFail = False yavgGrade = 94.4 yŽ-"

Created by George Zhao54

Variants

yVariables not restricted to specific type yNo need to declare by type ysize = 30 ylocation = "Hamilton Hall" ypassFail = False yavgGrade = 94.4 yŽ-"

Created by George Zhao55

Multiple Similar Variables

ySuppose we want to store the price of pineapple for each day of the week yWe can declare 7 separate variables: yDifficult to keep track of all of the variables yDifficult to access each of the variables yGets particularly difficult when the number of entries grow higher

Created by George Zhao56

Solution: Arrays

yArray: block of pigeonholes y7 pigeonholes, each representing a day of week:

Index0123456

Price$5.03$0.13$1.51$7.75$7.24$1.99$0.64

Created by George Zhao57

Arrays

yDim prices(6) as Double yprices(1) to retrieve entry from index 1 (second entry) yprices(7) will give out-of-bounds error yBenefit: the index can be accessed by other variables: yDim i as integer yprices(i) gives the (i+1)th entry

Created by George Zhao58

Arrays

yprices(0) = 5.03 yprices(1) = 0.13 yprices(2) = 1.51 yprices(3) = 7.75 yprices(4) = 7.24 yprices(5) = 1.99 yprices(6) = 0.64

Index0123456

Price$5.03$0.13$1.51$7.75$7.24$1.99$0.64

Created by George Zhao59

Multidimensional Array

yRow x column yDim matrix(1,2) as Integer yCreates 2x3 matrix with 2 rows, 3 columns matrix(0,0)matrix(0,1)matrix(0,2) matrix(1,0)matrix(1,1)matrix(1,2)

Created by George Zhao60

Dynamic Array

yDim sample(9) as String yCreates string array of size 10 ysample(0) = "Introduction" yNow suppose we want to increase the array size to 100 yReDim sample(99) cell index 0 yReDim Preserve sample(99) yThis preserves existing data and changes size

Created by George Zhao61

Practice Exercise

Dim dat(2, 1)

dat(0, 0) = "Criterion" dat(0, 1) = "Value" dat(1, 0) = "Budget" dat(1, 1) = 5123.21quotesdbs_dbs17.pdfusesText_23