How Create Functions And Subroutines In Excel 2007

More on Subs in Excel VBA

And then far, all of your code has been written in Subs. It's time to have a closer expect at them. Later on you've taken a closer look at how Subs work, we'll examine what a Function is, and you lot'll acquire how to create your own.

Subroutines

A Sub is a small chunk of code that you write to do a specific job. Yous tin run this Sub past pressing F5 in the VBA Editor, you can run it by assigning the Sub to a button on a spreadsheet, and you tin can even run it from the menu bar at the top of the Editor. In fact, there's quite a lot of dissimilar means you can run your Subs.

Ane other fashion to run a Sub is from another Sub. Let's see how.

Start with a new bare workbook. Open up up your VBA Editor (you should know how to do this by at present). Add together the following Sub:

Sub FirstCode( )

Dim FormatCell Every bit Integer

FormatCell = ActiveCell.Value

If FormatCell < 20 And then

With ActiveCell.Font

.Bold = True
.Proper noun = "Arial"
.Size = "16"

End With

End If

End Sub

All the code does is to format a jail cell depending on the value of ActiveCell. If ActiveCell is beneath 20 so we alter the font to assuming, Arial, sixteen points.

Now this code is fine every bit information technology stands. Just suppose we wanted to use this aforementioned code again. After all, we may to format cells farther downwards the Sub, or from another Sub entirely. Wouldn't it exist meliorate if we placed the formatting code in its own subroutine? That way, we could telephone call the formatting code into action whenever we needed it. Nosotros wouldn't have to duplicate code that we've already written.

Add some other Sub to your lawmaking. Call it SecondCode. Move the With Statement to your new Sub. Your coding window should at present look like this:

So we have ii Subs, one called FirstCode and i chosen SecondCode. We could put a button on the spreadsheet and and then attach the macro FirstCode to information technology. But this wouldn't be much good, as the SecondCode is not beingness chosen into action.

The way you activate i Sub from inside another is to just type its name. Optionally, yous tin can add the word Phone call at the showtime of the line. Like this:

Call SecondCode

That's enough to active the Sub y'all want to telephone call into activeness. VBA then executes the whole of the second Sub before returning to where it was.

Add together that line to your first Sub and it volition expect similar this:

Inside the If statement above, nosotros have the phone call to SecondSub. Once VBA has executed all the code for the second sub it returns to where information technology was. It so drops downwards and executes the lawmaking for the rest of the FirstCode sub. The adjacent line that VBA executes, therefore, would be the End If line.

Y'all tin try it out at this stage. On your spreadsheet, type a number less than twenty into any cell. Now get back to your coding window. Click anywhere inside of the Sub and Finish Sub of FirstCode. Press F5 on your keyboard to run your FirstCode Sub. Now expect at your spreadsheet over again. You should see that the number in the cell has the new formatting.

Nosotros mentioned that the commencement Sub could be assigned to a button on a spreadsheet. To practice so, you'd have to bring up the Assign Macro dialogue box. If you did, yous'd see that both Subs are on the list of Macros:

The reason they both show upwardly is that by default they are Public Subs. This means that they tin can exist seen just about everywhere from Excel. If you don't desire a Sub showing up in the Assign Macro dialogue box then you can make it Private. The mode y'all make a Sub Private is by typing the word Private before the word Sub:

Private Sub SecondCode( )

Because information technology has the word Private at the start, this Sub won't evidence up in the Assign Macro dialogue box:

In the adjacent lesson, you'll see how to pass values to your Subs. Save your work and proceed below.

Passing Values to a Sub >

<< Dorsum to the Excel VBA Home Page

Lots more than free online courses here on our main Home and Learn site

© All course material copyright Ken Carney

Source: https://homeandlearn.org/excel_vba_subroutines.html

Posted by: duboisgivat1997.blogspot.com

Share this post