E.S. INSTITUTO DE EDUCACIÓN SECUNDARIA

TABLA DE EQUIVALENCIAS

I. E.S. INSTITUTO DE EDUCACIÓN SECUNDARIA

6 Just a pinch of VBA

Tip 6.0 : Using

Tip 6.0 : Using the Macro Recorder in Microsoft Excelthe Macro Recorder in Microsoft Excel Open Excel and the

Open Excel and the VBEVBE ((Visual Basic EditorVisual Basic Editor). Unless it has been changed, the). Unless it has been changed, the VBEVBE

window contains the

window contains theProject Explorer Project Explorer window and thewindow and thePropertiesPropertieswindow (these can be accessedwindow (these can be accessed

from the

from the ViewViewmenu).menu).

Project Explorer:

Project Explorer: Works like a file manager. Helps you navigate around the code in Works like a file manager. Helps you navigate around the code in your your

workbook.

Properties Window:

Properties Window: Shows the properties of the currently acShows the properties of the currently active object (e.g.tive object (e.g. Sheet1Sheet1) of the) of the

current workbook (e.g.

current workbook (e.g.Book1Book1).).

Exercise 1: Recording a Macro. Exercise 1: Recording a Macro.

This exercise shows what happens when a macro is recorded and demonstrates the difference This exercise shows what happens when a macro is recorded and demonstrates the difference between recording absolute and relative references.

between recording absolute and relative references.

1. On an emptyOn an empty worksheetworksheet in a new workbook, select cellin a new workbook, select cellC10C10

2. Start theStart theMacro Recorder Macro Recorder with option to save macro inwith option to save macro inThis Workbook This Workbook . At this point the. At this point the

VBE

VBE creates a newcreates a newModulesModulesfolder. It's quite safe to go and look at folder. It's quite safe to go and look at it - your actions won't beit - your actions won't be

recorded. Click the

recorded. Click the [+][+]next to the folder and see that next to the folder and see that thetheVBEVBE has places a module in the folder has places a module in the folder

and named it

and named itModule1Module1. Double-click the module icon to open its code window. Switch back to. Double-click the module icon to open its code window. Switch back to

Excel.

3. Make sure that theMake sure that theRelative ReferenceRelative Referencebutton on thebutton on theStop Recording Stop Recording toolbar is NOTtoolbar is NOT

pressed in.

4. Select cellSelect cellB5B5and stop the recorder.and stop the recorder.

5. Switch to theSwitch to the VBEVBE and look at the code:and look at the code:

Range("B5").Select Range("B5").Select 6.

6. Now record another macro, exactly the same way, but thisNow record another macro, exactly the same way, but this timetime with thewith theRelativeRelative Reference

Referencebutton pressed in.button pressed in.

7. Switch to theSwitch to the VBEVBE and look at the code:and look at the code:

ActiveCell.Of

ActiveCell.Offset(-5, fset(-5, -1).Range("A1").Sel-1).Range("A1").Selectect 8.

8. Now record another macro, but instead of Now record another macro, but instead of selectingselectingcell B5, select a block of cells 3x3cell B5, select a block of cells 3x3

starting at B5 (select cells B5:F7)

9. Switch to theSwitch to the VBEVBE and look at the code:and look at the code:

ActiveCell.Of

ActiveCell.Offset(-5, fset(-5, -1).Range("A1:B3").S-1).Range("A1:B3").Selectelect 10.

11.

11. Run Macro2 but force an error byRun Macro2 but force an error byselectingselecting a cell in row 5 or above. The macro a cell in row 5 or above. The macro tries totries to

select a non-existent cell because its code is telling it to select a ce

select a non-existent cell because its code is telling it to select a cell 5 rows above the startingll 5 rows above the starting

point, and that's off the top of the sheet.

point, and that's off the top of the sheet. PressPressDebugDebugto be taken to the pato be taken to the part of the macro thatrt of the macro that

caused the problem.

NOTE: When the

NOTE: When the VBEVBEis in Debug mode the line of code is in Debug mode the line of code that caused the problem is highlightedthat caused the problem is highlighted

in yellow. You must "reset" the macro before you can proceed. Click the

in yellow. You must "reset" the macro before you can proceed. Click the ResetResetbutton on thebutton on the

VBE

VBE toolbar or go totoolbar or go to Run > ResetRun > Reset. The yellow highlighting disappears and . The yellow highlighting disappears and thetheVBEVBE comes outcomes out

of Debug mode.

12.

12. It is important to try and anticipate user error like this. The simplest way is to modify the codeIt is important to try and anticipate user error like this. The simplest way is to modify the code

to simply ignore

to simply ignore errorserrorsand move on to the nand move on to the next task. Do this by adding the line…ext task. Do this by adding the line…

On Error Resume Next

… immediately above the first line of the macro (underneath the line

… immediately above the first line of the macro (underneath the line SubSubMacro1()Macro1() 13.

13. RunRunMacro2 Macro2 as before, starting too high on the sheet. Thisas before, starting too high on the sheet. This timetime the line you typed tellsthe line you typed tells

Excel to ignore the line of code that it can't execute.

Excel to ignore the line of code that it can't execute. There is no error message and the macroThere is no error message and the macro

exits having done all it can. Use this method of handling

exits having done all it can. Use this method of handling errorserrorswith caution. This is a verywith caution. This is a very

simple macro. A more complex macro would probably not perform as expected if

simple macro. A more complex macro would probably not perform as expected if errorserrors werewere

simply ignored. Also, the user has no idea that something has gone

simply ignored. Also, the user has no idea that something has gone wrong.wrong.

14.

14. Modify the code of Modify the code of Macro2 Macro2 to include a more sophisticated error handler thus:to include a more sophisticated error handler thus:

Sub

SubMacro2()Macro2()

On Error GoTo

On Error GoTo ErrorHandler ErrorHandler ActiveCell.Of

ActiveCell.Offset(-5, fset(-5, -1).Range("A1").Sel-1).Range("A1").Selectect Exit Sub

Exit Sub

ErrorHandler: ErrorHandler:

MsgBox

MsgBox"You must start below Row 5""You must start below Row 5" End Sub

End Sub

15.

15. ThisThis timetimethe user is presented with a dialog box when something goes wrong. If there the user is presented with a dialog box when something goes wrong. If there isis

no error the line

no error the line Exit SubExit Sub causes the macro to finish after it has done its causes the macro to finish after it has done its job - otherwise the user job - otherwise the user

would see the message even if there were no

would see the message even if there were no error.error.

Improving Recorded

Improving Recorded MacrosMacros

The good way to learn the basics of VBA is to

The good way to learn the basics of VBA is to record a macro and see how record a macro and see how Excel writes its ownExcel writes its own

code. Often, though, recorded

code. Often, though, recorded macrosmacroscontain much more code than is necessary. Thecontain much more code than is necessary. The

following exercises demonstrate how you can improve and streamline code that has been

produced by a recorded macro.

Exercise 2: Improving on Recorded

Exercise 2: Improving on Recorded MacrosMacros This exercise shows that when

This exercise shows that when macrosmacros are recorded, often more code is generated thanare recorded, often more code is generated than necessary. It demonstrates the use of the With statement to précis the code.

necessary. It demonstrates the use of the With statement to précis the code.

1. Select any cell or block of cells.Select any cell or block of cells.

2. Start the macro recorder and cStart the macro recorder and call the macro FormatCells. The Relative References settingall the macro FormatCells. The Relative References setting

will not be relevant.

3. Go toGo toFormat > Cells > FontFormat > Cells > Font and chooseand chooseTimes New RomanTimes New RomanandandRed Red ..

Go to

Go toPatternsPatternsand chooseand chooseYellow Yellow ..

Go to

Go toAlignmentAlignmentand chooseand chooseHorizontal, Center Horizontal, Center

Go to

Go toNumber Number and chooseand chooseCurrency Currency ..

4. ClickClickOKOKand stop the recorder.and stop the recorder.

5. Click theClick theUndoUndobutton (or button (or Ctrl+Z Ctrl+Z ) to undo your changes to ) to undo your changes to the worksheet.the worksheet.

6. Select a block of cells and run theSelect a block of cells and run the FormatCellsFormatCellsmacro. Note that it can not be undone! Typemacro. Note that it can not be undone! Type

in the cells to check the result of the formatting.

7. Look at the code:Look at the code:

Sub

SubFormatSelection()FormatSelection() Selection.NumberF

Selection.NumberFormat = ormat = "$#,##0.00""$#,##0.00" With

WithSelectionSelection .HorizontalAlignm

.HorizontalAlignment = ent = xlCenter xlCenter .VerticalAlignm

.VerticalAlignment = ent = xlBottomxlBottom .WrapText =

.WrapText = FalseFalse

.Orientation = 0 .Orientation = 0 .ShrinkToFit =

.ShrinkToFit = FalseFalse

.MergeCells =

.MergeCells = FalseFalse

End With

With

WithSelection.FontSelection.Font

.Name = "Times New Roman" .Name = "Times New Roman" .FontStyle = "Regular"

.FontStyle = "Regular" .Size = 10

.Size = 10 .Strikethrough =

.Strikethrough = FalseFalse

.Superscript =

.Superscript = FalseFalse

.Subscript = .Subscript = FalseFalse

.OutlineFont =

.OutlineFont = FalseFalse

.Shadow = .Shadow = FalseFalse

.Underline =

.Underline = xlUnderlineStyxlUnderlineStyleNoneleNone .ColorIndex = 3

.ColorIndex = 3 End With

End With

With

WithSelection.Interior Selection.Interior .ColorIndex = 6 .ColorIndex = 6 .Pattern = xlSolid .Pattern = xlSolid

8. Note all the extra instructions that have been recorded. Delete lines of code so Note all the extra instructions that have been recorded. Delete lines of code so that only thethat only the

following remains:

Sub

SubFormatSelection()FormatSelection() Selection.NumberF

Selection.NumberFormat = ormat = "$#,##0.00""$#,##0.00" With

WithSelectionSelection .HorizontalAlignm

.HorizontalAlignment = ent = xlCenter xlCenter End With

End With

With

WithSelection.FontSelection.Font

.Name = "Times New Roman" .Name = "Times New Roman" .ColorIndex = 3

.ColorIndex = 3 End With

End With

With

WithSelection.Interior Selection.Interior .ColorIndex = 6 .ColorIndex = 6 End With End With End Sub End Sub 9.

9. Run the macro to test the edited code. It still works as Run the macro to test the edited code. It still works as before.before.

10.

10. Now modify the code even further:Now modify the code even further:

Sub

SubFormatSelection()FormatSelection() With

WithSelectionSelection

.NumberFormat = "$#,##0.00" .NumberFormat = "$#,##0.00" .HorizontalAlignm

.HorizontalAlignment = ent = xlCenter xlCenter .Font.Name =

.Font.Name = "TimesNewRoma"TimesNewRoman"n" .Font.ColorIndex = 3 .Font.ColorIndex = 3 .Interior.ColorIndex = 6 .Interior.ColorIndex = 6 End With End With End Sub End Sub 11.

11. Test the macro. Everything still works and the code will run much faster.Test the macro. Everything still works and the code will run much faster.

12.

12. Try recording the same macro using toolbar buttons instead of going to the dialog box:Try recording the same macro using toolbar buttons instead of going to the dialog box:

Change the Font to

Change the Font to Times New RomanTimes New Roman

Change the Font Colour to

Change the Font Colour toRed Red

Change the Fill Color to

Change the Fill Color to Yellow Yellow

Click the

Click the Center Center buttonbutton

Click the

Click the Currency Currency buttonbutton

13.

13. Look at the code. You still get lots of stuff that you Look at the code. You still get lots of stuff that you don’t necessarily want. Excel is recordingdon’t necessarily want. Excel is recording

all the

all the default default settings. Most of these are safe to delete.settings. Most of these are safe to delete.

14.

14. Experiment with editing directlExperiment with editing directly into the code to change y into the code to change colours, the font, the number formatcolours, the font, the number format

etc.

Exercise 3: Watch a

Exercise 3: Watch a Macro Being RecordedMacro Being Recorded

This exercise shows that you can learn by watching the macro build as

This exercise shows that you can learn by watching the macro build as it is being recorded. It isit is being recorded. It is also an example of

also an example of when sometimes the With statement isn’t appropriate.when sometimes the With statement isn’t appropriate.

cells can cause problems. Try

cells can cause problems. Try filtering thefiltering the datadata and see what happens. Go toand see what happens. Go toData > Filter >Data > Filter > Autofilter

Autofilterand filter by Region or Month. It is clear that Excel does not and filter by Region or Month. It is clear that Excel does not make the samemake the same

assumptions that the user does. The empty cells need to be

assumptions that the user does. The empty cells need to be filled.filled.

2. Tile the Excel andTile the Excel and VBEVBE windows (vertically) so they are side-by-side.windows (vertically) so they are side-by-side.

3. Select any cell within the data. If it is an empty cell it must be adjacent to Select any cell within the data. If it is an empty cell it must be adjacent to a cell containinga cell containing

data.

4. Start the macro recorder and call the macroStart the macro recorder and call the macroFillEmptyCellsFillEmptyCells. Set to record. Set to recordRelativeRelative References

References..

5. In theIn the VBEVBE windowwindow findfind and double-click the module (Module1) for and double-click the module (Module1) for the current workbookthe current workbook

to open the editing pane, then switch off the Project Explorer window and the Properties window

(only to make space).

6. Record the new macro as follows:Record the new macro as follows:

Step 1.

Step 1. Ctrl+*Ctrl+*(to select the current region)(to select the current region)

Step 2.

Step 2. Edit > Go To > Special > Blanks > OKEdit > Go To > Special > Blanks > OK (to select all the empty cells in the current region)(to select all the empty cells in the current region)

Step 3. Type

Step 3. Type =[UpArrow]=[UpArrow] then pressthen pressCtrl+Enter Ctrl+Enter (to place your typing into all the selected cells)(to place your typing into all the selected cells)

Step 4.

Step 4. Ctrl+*Ctrl+*(to select the current region again)(to select the current region again)

Step 5.

Step 5. Ctrl+CCtrl+C(to copy the selection - any method will do)(to copy the selection - any method will do)

Step 6.

Step 6. Edit > PasteEdit > PasteSpecial > Values > OKSpecial > Values > OK (to paste the(to paste the datadata back into the same place butback into the same place but

discarding the

discarding the formulasformulas))

Step 7.

Step 7. EscEsc(to come out of Copy Mode)(to come out of Copy Mode)

Step 8. Stop Recording.

7. Look at the code:Look at the code:

Sub

SubFillEmptyCells()FillEmptyCells()

Selection.CurrentRegion.Select Selection.CurrentRegion.Select Selection.SpecialC

Selection.SpecialCells(xlCellTells(xlCellTypeBlanks).SelectypeBlanks).Select Selection.FormulaR Selection.FormulaR1C1 = 1C1 = "=R[-1]C""=R[-1]C" Selection.CurrentRegion.Select Selection.CurrentRegion.Select Selection.Copy Selection.Copy Selection.PasteSpeci

Selection.PasteSpecial al Paste:=xlValPaste:=xlValues, ues, Operation:=xlNone, SkipBlanks:= _ Operation:=xlNone, SkipBlanks:= _ False

False, Transpose:=, Transpose:= FalseFalse

Application.CutCo

Application.CutCopyMode pyMode == FalseFalse

End Sub

Tip 6.1 : Sum the digits numbers in a cell using custom VBA function in

Microsoft Excel

•

• Alt+F11 to openAlt+F11 to openVisual Basic EditorVisual Basic Editor (VBE).(VBE). •

• From the Insert menu, select From the Insert menu, select Module (to add a Module (to add a module).module). •

• Enter the following lines of code into Enter the following lines of code into the module:the module:

Function

Function SumDigits(SumDigits(Number)Number)

Dim i As Integer

For i = 1 To Len(Number)

SumDigits = SumDigits + Val(Mid(Number, i, 1))

Next i

End Function

To test the function: To test the function:

•

• To test the To test the function, open Paste Function (Shift+F3).function, open Paste Function (Shift+F3). •

• From the User Defined category, select the From the User Defined category, select the SumDigits function.SumDigits function. •

• In the function box, select any cell In the function box, select any cell that contains a date.that contains a date. •

• Click OK.Click OK.

Tip 6.2 : Hide an excel sheet without the option of unhiding it using VBA in

Microsoft Excel

To hide a sheet so that

To hide a sheet so that no body can unhide it without knowing theno body can unhide it without knowing thepasswordpassword for thefor the

Vb Project, simply open the VB Editor and change the visible property to 2 i.e.

xlsheetveryhidden.

The sheet should disappear from the Unhide sheets

The sheet should disappear from the Unhide sheets commandcommand..

The only way to unhide it

The only way to unhide it is to change the property back to 0 i.e.is to change the property back to 0 i.e.

xlSheetNormal using the password to the VBProject.

Tip 6.3 : Convert negative va

Tip 6.3 : Convert negative values treated as text using VBA lues treated as text using VBA in Microsoftin Microsoft

Excel

Excel When you

When you importimport numbers fromnumbers from datadata sources outside Excel it sometimes happenssources outside Excel it sometimes happens

that the negative values

a =

a = Selection.Areas.CouSelection.Areas.Countnt If a = 1

If a = 1 And Selection.Cells.Count = 1 Then ActiveSheet.UsedRange.SelAnd Selection.Cells.Count = 1 Then ActiveSheet.UsedRange.Selectect For a =

For a = 1 To Selection.Areas.Count1 To Selection.Areas.Count For Each cl

For Each cl In Selection.Areas(a).CellIn Selection.Areas(a).Cellss If Right(cl.Formula, 1) = "-" Then If Right(cl.Formula, 1) = "-" Then

cl.Formula = "-" &

cl.Formula = "-" & Left(cl.Formula, Len(cl.FormLeft(cl.Formula, Len(cl.Formula) - 1)ula) - 1) End If End If Next cl Next cl Next a Next a Application.Stat

Application.StatusBar = usBar = FalseFalse Application.ScreenU

Application.ScreenUpdating = pdating = TrueTrue End Sub

End Sub

In document 2. Autoridades y personal (página 66-72)