Running a subroutine that resides in another file

Loading...

來自 University of Colorado Boulder 的課程

Excel/VBA for Creative Problem Solving, Part 1

650 個評分

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

650 個評分

課程 1（共 3 門，Specialization Excel/VBA for Creative Problem Solving）

"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.

從本節課中

User-Defined VBA Functions

In Week 2, you will learn basic VBA expression entry, how to create user-defined functions, how to convert functions to Add-Ins in Excel, how to borrow Excel's built-in functions, how to troubleshoot your VBA functions when they aren't working, and you'll learn about how to design your procedures in a modular fashion.

Week 2 preview1:54

How to write basic VBA expressions and use built-in functions6:51

How to make your own user-defined function4:35

Example of a user-defined function3:19

How to convert a user-defined function to an Excel Add-In4:24

Borrowing Excel functions4:07

How to troubleshoot your user defined functions1:06

Why you don't use input and message boxes in functions2:58

An introduction to modular programming5:34

Running a subroutine that resides in another file4:17

與講師見面

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

I've shown you how you can run subroutines that are in the same file.

You can use the call statement, so they can either be in the same module or

in a different module.

But what I wanted to show you here,

is how you can run subroutines that are in different files.

So I've got two files open.

I've got this main one, and then I've got one called RunMe.

I'm just going to show you have you can run the module.

So, if I open up the module in RunMe,

it's just a very simple subroutine that just going to do message box hello.

So back in the main file, which I call application.run example.

You can use this application.run method, and

you can put the file name here, with an exclamation point,

with the name of the sub in that file.

And importantly, this is pretty important,

if your file has spaces in it, then you need to have apostrophes here.

If you didn't have any apostrophes in your file, you can just delete those and

it will then just being close in the quotations.

But since I have a space in Run space Me, then I need single apostrophes there.

And this is similar to other things that you do with files in VBA.

So when I run this up it's going to run the RunMe sub in this file.

So it's going to go into this file and run it, all right?

Now importantly, this file, the file that is referenced using the application.run.

This file does not have to be open, so I'm going to go ahead and close that.

So I'm going to go ahead and close RunMe.

And now let's go back and I'm going to run it.

So I'm going to press F8 to skip through this.

It opens up the RunMe file, and runs the RunMe sub, all right?

Now you notice though, unfortunately, it leaves you with the RunMe file open.

So I'm going to go ahead and close that.

Now let me show you how we can change that.

I've modified the code quite a bit, so this is all just so

that the file when it opens it'll be closed again.

So I'm going to Dim FileName As String, aWB As Workbook,

this is sort of more complex VBA stuff.

But I wanted to just point it out now,

while we're talking about how to open up subs and other modules and workbooks.

Then you can say FileName, there's a Application.GetOpenFilename,

and I'll show you what that is in a minute.

It kind of brings up a pop-up box,

where you can select the file that you want to run.

So that's going to be the file that has our RunMe sub in it,

and then we open that workbook.

So we open the workbook that we select, we set this variable, that's dimmed,

that's a workbook equal to the active workbook.

We need do that because we are going to be closing that and then we run, so

this is the single line I was in the previous example.

Then after running that we can close the active workbook that we set up here.

And we're going to save changes equals false.

So we're not going to save any changes that by chance were made.

So let's go ahead and run through this.

And again, if you don't really understand what's going on here, that's just fine.

I kind of wanted to just include this for your reference.

So let's go ahead and do F8.

That brings up this get open file name.

So I'm going to choose where that subroutine is located

and then it runs that file.

So I click OK, and then it'll actually close the file, all right?

So it doesn't leave it open like the first thing I did result in.

So that's just another way that you can run a subroutine.

In this case, in a different file altogether.

探索我們的目錄

免費加入並獲得個性化推薦、更新和優惠。

Coursera 致力於普及全世界最好的教育，它與全球一流大學和機構合作提供在線課程。

© 2019 Coursera Inc. 保留所有權利。

通過 App Store 下載

通過 Google Play 獲取