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.
Practice Video: Edit a Macro
Loading...
来自 Macquarie University 的课程
Excel Skills for Business: Intermediate II
247 个评分
从本节课中
Recording Macros
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.
与讲师见面
Dr Yvonne BreyerDirector, Learning and Teaching
Faculty of Business and Economics
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.
