Explore
For Enterprise
Join for Free
Log In

Kunst und Geisteswissenschaften
Wirtschaft
Informatik
Datenverarbeitung
Informationstechnologie
Gesundheit
Mathematik und Logik
Persönliche Entwicklung
Physik und Ingenieurwesen
Sozialwissenschaften
Sprachen lernen
Abschlüsse
Zertifikate

Alle Bereiche erkunden

Blättern
Suche
Für Unternehmen
Anmelden
Kostenlose Teilnahme

Declaration of variables, data types, and scope of variables

Loading...

Excel/VBA for Creative Problem Solving, Part 1

University of Colorado Boulder

4.9 (966 ratings) | 33K Students Enrolled

Kurs 1 von 3 in Excel/VBA for Creative Problem Solving Spezialisierung

"Excel/VBA for Creative Problem Solving, Part 1" is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). This course is the first part of a three-part series and Specialization that focuses on the application of computing techniques in Excel/VBA to solve problems. In this course (Part 1), you will: 1) create macros to automate procedures in Excel; 2) define your own user-defined functions; 3) create basic subroutines to interface with the user; 4) learn the basic programming structures in VBA; and 5) automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems. New to computer programming? The extremely intuitive and visual nature of VBA lends itself nicely to teaching and learning - what a fun way to learn to code! No prior knowledge in programming nor advanced math skills are necessary yet seasoned programmers will pick up new and creative spreadsheet problem solving strategies. After you have learned the basics of VBA, each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

Lehrplan anzeigen

Skills You'll Learn

Microsoft Excel Vba, Computer Programming, Problem Solving, Debugging

Bewertungen

4.9 (966 ratings)

5 stars
848 ratings
4 stars
108 ratings
3 stars
7 ratings
2 stars
1 ratings
1 star
2 ratings

SH

Oct 27, 2018

Really enjoyed this course. From Week 4 onwards it's quite challenging. Really appreciate the active engagement from Dr. Nuttelman and other moderators on the forum who gave excellent advice.

RP

Apr 14, 2018

I really learned a lot in this course. I could start using right away what i've learned in real life problems. And it challenged me to discover new tools in excel and vba. Thank you!!

Aus der Unterrichtseinheit

Macro recording, VBA procedures, and debugging

Week 1 will introduce you to visual basic for applications (VBA) and teach you the foundational tools required to create basic procedures in VBA. You'll learn about different data types and the scope of variables, how to troubleshoot your code when it's not working, and how to record basic macros using both absolute and relative referencing modes.

Week 1 preview1:58

Getting your feet wet4:05

Saving your files as macro-enabled workbooks0:57

Recording basic macros8:08

Absolute vs. relative referencing during macro recording8:27

Overview of procedures in VBA8:15

Why use Option Explicit?3:19

Declaration of variables, data types, and scope of variables8:39

How to troubleshoot when your code is not working properly6:18

Unterrichtet von

Charlie Nuttelman
Instructor

Skript

Hi there, I'm going to kind of explain how to declare variables and I'm

going to talk about data types, and the scope of variables in this screen cast.

So, sure a lot of you have wondered what it means to have dim statements.

So first example, we have a dim statement here,

we're dimming all these different things as doubles.

So I'm going to kind of talk about what those are.

All variables, if you have the Option Explicit,

need to be declared that's what Dim means.

Dim is for dimensionalize, you basically set aside space for it.

So the most common type of data in VBA is a double.

That's used for numbers with decimal fractions,

and high precision exponents extended range.

This is essentially, you can think of a double as a real number.

The precision is 15 to 16 significant figures.

Integer is another common data type, strings are also quite common.

We're going to have a whole module just working with strings.

Single used to be one of the most common types before,

because it uses only half the memory as doubles.

Long, which goes from -2 billion to 2 billion they're integers.

Booleans are a data type which is true or false.

If you don't dim the default is variant, and it's sort of just accordingly.

So it sort of detects what it is, and then another common one would be range.

You can also dim things as currency, as dates, as there's all sorts of data types.

Let's talk real quick about the scope of a variable and

that is how far is the reach of the variable and where it can be seen.

So, you can make it so it's only within a single procedure.

So, it's local to either a sub or a function.

This is declared using a Dim or

Static statement within the procedure, or it can be within the current module.

So you can dim something and you can declare a variable and

it will be available throughout the entire module.

So I've got an example on that.

You can also use the Public statement to Dim it in all procedures and

all modules open in the current workbook.

So just kind of an example, we might have a workbook this blue square

rectangle we might have two modules.

If you dim k outside of the module, then that means

k here is going to be available to both Sub One and this Funky function.

Y here is only going to be available to the Funky function,

it's not going to be able to be seen anywhere else.

We've got this public R, if you make it public then this is

going to be available to anything in the workbook, not just in module 2.

So we could Reference that, we wouldn't have to re-dim it in any of these modules.

J here is only in Sub Two, and when you're making functions,

so the argument of the function it's just local to that function.

So let's work through an example, I've got a sub here.

We just calculate pi 4 times the arctangent of 1,

we get the radius of a sphere.

In an input box, we calculate the surface area.

We output that in a message box, and then I subcontract out

the work of calculating the volume to this SphereVolume function.

So let's go ahead and step through this using F8, we obtain this in

an input box, we calculate the area, we message box that.

The surface area is 314, and

now we go into the function.

But you notice that we have a problem here because pi

hasn't been declared within the sphere volume function.

So to bypass that, you could dim pi as a double.

We also need to define what pi is separately in this function.

So now when we do this, when me run the sphere.

Enter the radius of 5, it displays the area, and it also displays the volume.

However, if we wanted to we can dim pi outside at the modular level,

so I'm going to eliminate this.

I'm going to delete pi here, and the pi

here, I'm going to dim outside of the procedures.

So this is at the modular level.

You notice I'm using pi here and assuming

we define it in a kind of step wise fashion when we jump into the function

here it's already going to have the value of pi because pi is at the modular level.

And so let's go ahead and run this, we get 5 and

we get the surface area, and also the volume.

So we don't have that error that we did when we dimmed pi inside the module.

So what if this function were in a different module, so in Module 2.

If that's the case, it's not going to work because dimming pi outside of

the procedure here, this is only going to be available to all procedures in Module1.

So to bypass this, we can use the public statement.

So now, pi is going to be available to everything in the workbook.

So any module in this workbook, including Module2, in which we have pi.

So if you can see, when we step through this, that it goes in

to the function on Module2, but we still have the value of pi.

And we message box that, and that's exactly what we got earlier.

So if you're working with procedures that are in different modules and

you want to be able to pass the value without re-dimming,

then this is how you can do it.

And it's tremendously useful if you know what you're doing.

All right, real quick I wanted to explain what this static statement is.

So here I just have a non-static example.

This sub I'm dimming d as an integer and

I'm just saying d = d + 1 and I message box that.

So let me show you what that does, we run the non-static and so

I run it, it gives us a message box of 1.

We run it, it's 1, I run again, it's 1.

All right, so it basically resets the variable every time you run it.

However, if you static, if you instead of dim, you can write Static d As Integer.

It’s exactly the same as the sub up here, but let me show you what happens.

So that’s this run static, we run it once and it displays 1, run it again, it’s 2.

So it always keeps the value and we’re adding 1 every time.

If you close the file and reopen it, it’s set to a zero,

but this is how you can remember the value of a parameter.

So here's kind of a cool example of some more

complex things we can do with this static variable.

This sub is the same as it was, I've added this optional reset as Boolean.

And I have another line here, if reset, if that's true, then d = 0.

So there's this optional argument to a sub, and it's a Boolean.

So if reset is true, then we're going to reset d to 0, all right?

So this is how we can reset a static variable back to 0,

and we have to run the reset from another subroutine.

So all this does is it runs the static example and

passes the argument of true through here.

So when we run this sub, it's going to pass true,

it's going to then reset d to 0.

So let's go ahead and show you what happens and

we're still running the static.

1, 2, 3, 4, 5, and so on and now when I do reset, it resets d to 0,

but the the first line is the incremented it by 1 and we get the message box.

So I hope you learned a lot about declaration of variables, data types, and

the scope of variables.

Thanks for watching.

Durchsuchen Sie unseren Katalog

Melden Sie sich kostenlos an und erhalten Sie individuelle Empfehlungen, Aktualisierungen und Angebote.

Coursera arbeitet mit erstklassigen Universitäten und Organisationen zusammen, um Online-Kurse anzubieten und dadurch universellen Zugriff auf die weltweit beste Ausbildung zu ermöglichen.

© 2019 Coursera Inc. Alle Rechte vorbehalten.

Aus dem App Store herunterladen

Erhältlich bei Google Play

Coursera
Info
Leitung
Jobs
Katalog
Zertifikate
MasterTrack™ Certificates
Abschlüsse
Für Unternehmen
Für Regierungen

Community
Learners
Partners
Developers
Beta Testers
Translators

Verbinden
Blog
Facebook
LinkedIn
Twitter
Instagram
Youtube
Tech-Blog

mehr
Bedingungen
Datenschutz
Hilfe
Zugänglichkeit
Presse
Kontakt
Verzeichnis
Partnerunternehmen