Loading...

Practice Video: Edit a Macro

Course video 45 of 48

We all love a bit of automation, and this module teaches you just that. By the time you have completed this module, you will be able to identify the uses of macros, as well as create, edit and manage them to increase your efficiency.

Excel Skills for Business: Intermediate II

麦考瑞大学

课程 3（共 4 门，Excel Skills for Business 专项课程）

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.

查看授课大纲

您将学习的技能

Lookup Table, Microsoft Excel Vba, Data Validation, Microsoft Excel Macro

教学方

Dr Yvonne Breyer
Program Director, Design and Experience, Global MBA

脚本

Alex has now successfully recorded and run his macro, and

it seems to be working really well.

The client however has come back and said that they don't like the size of

the heading, they want that to be a bit bigger.

And that they would really like the user to be prompted

to provide the week commencing date.

Now in the case of the heading, it will be crazy to delete the macro and

re-record the whole thing over such a small change.

So, what Alex would like to do is simply tweak the macro.

He also will find it hard to record the step of popping up a message asking

the user to enter the date, because there is no facility to do that in Excel.

So again, there's an example where he will need to edit the macro

rather than simply record it.

And that's what we're going to look at next.

To edit a macro, we're going to come back to our Developer tab, and again,

we're going to click on that big Macro's button.

And when we do, we're going to make sure we've got Create Timesheet selected, but

this time we're going to click Edit.

When you edit a macro, it will open up the VBA editor.

Your screen may look slightly different to mine, as this view can be customized.

The important things that you can see though should be the project explorer,

which allows you to look at different macros in different open workbooks, and

the code window on the right.

When you record a macro in Excel, it will automatically create a module.

And all the recorded macros are stored in modules.

Usually there's one, but there may be two or even more.

Having a look at the code now,

first thing you will notice at the top is a Sub Create_Timesheet.

The Sub stands for subroutine, and all recorded macros begin with a Sub.

In a similar way, all recorded macros end with an End Sub.

And it is very important that all of your code sits between the Sub and the End Sub.

Each line of code represents one instruction or

one step that you performed.

Right at the top though, you will notice that some of the code is green and

prefixed with an apostrophe.

These are what we call comments.

Comments are entirely ignored by the compiler, but

they're very useful for documenting our code.

So I could put in a comment here with my name and the day I wrote the code or

the day I changed the macro.

And I could write absolutely anything as it would be ignored by the compiler.

If there is a line that is in error but you're nervous about deleting it,

you can also simply put an apostrophe in front of that line, and

that will comment out the line.

Now, we might need this though, so I'm going to remove that.

Now, coming down into our code where it actually begins,

the first line is Sheets.Add After:=ActiveSheet.

Now, at first glance, that can be a little daunting.

But actually the code is quite easy to understand and

all this is saying is add a new worksheet after the sheet I'm currently clicked on.

Let's have a look at the third line.

Range("A2").Select, again, strangely worded, but

all that's saying is select cell A2.

So quite straightforward, actually, when you get into it.

Now the thing we want to change first is the size of the font.

And if you scroll down a little, you'll see With Selection.Font, size = 16.

So that's where we specified we wanted our size to be size = 16.

Well, we can just change that now to size 20.

Now what I've done here is I have changed the code.

This will have no impact on my existing worksheets.

You will only see this change take effect when we actually run the code again.

But there's one other change we also want to make.

We actually want to ask the user to enter the week's commencing date.

So I'm going to come up to the line Week Commencing.

Just click in the code at the end and press Enter, so I can get a new line.

Remember, each new instruction must start on a new line.

Then I'm going to type range("b2

because I want to change the value in b2, ").value,

because I'm changing the value rather than the font, or the border, or the color, and

then a space is equal to, and here comes the interesting bit.

We're going to use an input box.

So I'm just going to type inputbox and open my bracket.

There are two arguments I need to give input box.

The first is a prompt.

That's what the instruction I'm going to give the user.

So I'm going to open my quotes, and I'm going to say,

Please enter week commencing date, and close my quotes.

Then type a comma,

and this is the title of the box which is what's going to be at the top and

that's going to be, New Timesheet Date, close quotes, close your brackets.

And then if you want to just click away, you have now put in your second change.

Before we can now test this, we're going to need to save.

You will note a Save icon at the top here.

You can click that Save or you can go back into your workbook and press Save.

Both will save the entire workbook complete with the macro.

So click whichever one you prefer.

And now a nice little shortcut to get back to our Excel workbook,

we're going to press Alt+F11.

And that shortcut toggles you between the code window and the worksheet.

And now to test our macro.

So we're going to click back on to Sheet1 and

I'm going to click the New Timesheet button.

You'll see it's run the first two lines of code, and now it's asking for

us to type in the week commencing date.

And that's going to be the 02/10/17, and click OK.

It is now completed the macro and put our date in.

Just one little step that we haven't done is to widen our column sufficiently so

we can see the date.

So that's what we're going to look at how to do in the next video.

So we've now looked at how we can easily open a VBA code window and edit the code.

In the next video, we're going to look at how we can delete and copy macros.

关于 Coursera

课程、专项课程和在线学位均由全世界一流大学和教育机构的顶尖授课教师教授。

Join a community of 40 million learners from around the world

Earn a skill-based course certificate to apply your knowledge

Gain confidence in your skills and further your career

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

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

通过 App Store 下载

通过 Google Play 获取