vba programming part one. our dartmouth legacy 10 input "what is your name: "; u$ 20 print...
Post on 21-Dec-2015
214 views
TRANSCRIPT
![Page 1: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/1.jpg)
VBA Programming
Part One
![Page 2: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/2.jpg)
Our Dartmouth Legacy10 INPUT "What is your name: "; U$
20 PRINT "Hello "; U$
25 REM
30 INPUT "How many stars do you want: "; N
35 S$ = ""
40 FOR I = 1 TO N
50 S$ = S$ + "*"
55 NEXT I
60 PRINT S$
65 REM
![Page 3: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/3.jpg)
Startup Excel and hit…
Alt-F11
![Page 4: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/4.jpg)
![Page 5: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/5.jpg)
First things first: Getting Help
![Page 6: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/6.jpg)
Getting More Help
![Page 7: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/7.jpg)
Project Explorer
• Excel objects
• User Forms
• Code Modules
![Page 8: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/8.jpg)
Properties Window
Properties are an object's characteristics, e.g. a sheet's name.
![Page 9: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/9.jpg)
Immediate Window
![Page 10: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/10.jpg)
Using the Immediate Window
![Page 11: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/11.jpg)
Try the Immediate Window MsgBox("Hello!") x = 1 + 1 debug.Print x 'this is a comment x = WorksheetFunction.Sum(1,2) debug.Print x
![Page 12: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/12.jpg)
Insert a module
![Page 13: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/13.jpg)
Add some codeSub HelloWorld() MyName = InputBox("What is your name?") MsgBox ("Hi " + MyName)End Sub
Type "HelloWorld" in the Immediate Window to run your program.
Go back to Excel and run it from the Tools|Macro menu
![Page 14: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/14.jpg)
Subroutines vs. Functions Both are procedures: A named sequence
of statements executed as a unit. (MS) Functions return a value Subroutines don't
![Page 15: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/15.jpg)
Procedures take arguments
![Page 16: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/16.jpg)
Arguments are variables
![Page 17: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/17.jpg)
But what are variables? "A named storage location that can
contain data that can be modified during program execution." (MS)
1. Has name2. Contains data3. Can be modified
![Page 18: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/18.jpg)
Convert Celsius to Fahrenheit
Function Fahrenheit(Celsius) Fahrenheit = (Celsius * 1.8) + 32End Function
![Page 19: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/19.jpg)
Use the Function
![Page 20: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/20.jpg)
Back to the Immediate Windowx = 1debug.Print x 1 x = 2debug.Print x 2
'Set the value of x to 1
'Now set it to 2
![Page 21: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/21.jpg)
Making Decisions
![Page 22: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/22.jpg)
Decisions: If…ThenFunction VeryHot(Celsius) If (Celsius > 30) Then VeryHot = True Else VeryHot = False End IfEnd Function
![Page 23: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/23.jpg)
Which number is bigger?Function MyMax(x, y) If ( … ) Then … Else … End IfEnd Function
Remember: 'FunctionName = Result'
![Page 24: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/24.jpg)
Which number is bigger? (Solution)Function MyMax(x, y) If ( x > y ) Then MyMax = x Else MyMax = y End IfEnd Function
![Page 25: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/25.jpg)
Loops: For…NextFor Variable = BeginValue to EndValue
do something with VariableNext
Sub firstLoop() For x = 1 To 10 Debug.Print x NextEnd Sub
![Page 26: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/26.jpg)
Example: Sum of 1…N
Function SumToN(N) SumToN = 0 For x = 1 To N SumToN = SumToN + x NextEnd Function
![Page 27: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/27.jpg)
TemperatureChart Create a sub called TemperatureChart Use For…Next to loop Celsius from 0 to 40 Use debug.print to output the result of
converting Celsius to Fahrenheit using our function
![Page 28: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/28.jpg)
TemperatureChart (Solution)
Sub TemperatureChart() For Celsius = 0 To 40 Debug.Print Fahrenheit(Celsius) NextEnd Sub
![Page 29: VBA Programming Part One. Our Dartmouth Legacy 10 INPUT "What is your name: "; U$ 20 PRINT "Hello "; U$ 25 REM 30 INPUT "How many stars do you want: ";](https://reader038.vdocuments.us/reader038/viewer/2022110323/56649d605503460f94a408dc/html5/thumbnails/29.jpg)
What we have covered so far… The VBA environment Inserting a module Running a macro Writing a user-defined function Using the new function in Excel Variables If…Then…Else For…Next