How to insert VBA code in an Excel worksheet:

Goal: How to insert a VBA script into your worksheet. (not how to code VBA). Create a spreadsheet that uses a VBA coded function called isBold that shows true when a cell is bolded and false when it is not. Your spreadsheet will just have 2 cells, one that is bolded and one with the isbold function.

Open office note: This will not work for open office as it does not use VBA (but does use alternative scripting languages).

Here is a movie of this entire assignment: http://adelphi.hosted.panopto.com/Panopto/Pages/Viewer.aspx?id=b2ee4532-a62f-44fb-a325-141ef888352e

Step 1: Open a spreadsheet you want to insert a VBA script into

Step 2: Open the VBA code window and copy in the VBA code

· Hit <alt> + f11 to open code window

· Hit + on left to find “this workbook”

· Right click on “this workbook” (or on your worksheet) and choose insert / module

· Copy the code below and paste the following into the code window:

Function IsBold(rng As Range) As Boolean

Application.Volatile True

If IsNull(rng.Font.Bold) Then
IsBold = False
Else
IsBold = rng.Font.Bold
End If
End Function

· Close the Code window with an X

· Now you have a new command available to your spreadsheet.

Step 3: Back in your worksheet, use the new function

· In cell b2, type =IsBold(B1)

· Type something into b1 and make it bold

· Calculate by Choosing formula and then calculate now.

o (If you want to not have to press calculate now, you can put

Application.Volatile True

As the first line following function so that it will recalculate whenever any cell changes.)

· If the B1 is bold, it will show true, otherwise false.

· Change B1 to not be bold and then choose formula / calculate now

· You will need to save the file as a macro enabled spreadsheet (xlsm).

Step 4: Upload this small spreadsheet to moodle.

Note that This isBold script is taken from http://www.pcreview.co.uk/forums/can-you-use-if-then-statement-select-bold-values-list-t3909439.html

Here is one more example if you want to try it.

Another good script I often use is from http://www.techonthenet.com/excel/macros/extract_hl.php to Extract a hyperlink address from a cell:

Function HyperLinkText(pRange As Range) As String

Dim ST1 As String

Dim ST2 As String

If pRange.Hyperlinks.Count = 0 Then

Exit Function

End If

ST1 = pRange.Hyperlinks(1).Address

ST2 = pRange.Hyperlinks(1).SubAddress

If ST2 <> "" Then

ST1 = "[" & ST1 & "]" & ST2

End If

HyperLinkText = ST1

End Function

Note on Open Office: You cannot use VBA in Open Office, though you can create Basic, BeanShell, JavaScript, Java or Python macros. This document will help you explore basic language macros in Open Office: http://www.openoffice.org/documentation/manuals/userguide3/0312CG3-CalcMacros.pdf