option explicit
sub test_excelHelpers() ' {
test_findWorksheet
test_deleteRange
test_hyperlink
test_num_letter
end sub ' }
private sub test_findWorksheet() ' {
dim ws_foo, ws_bar, ws_baz as worksheet
set ws_foo = findWorksheet("foo")
set ws_bar = findWorksheet("bar")
set ws_baz = findWorksheet("baz")
ws_foo.cells(1,1) = "foo"
ws_bar.cells(1,1) = "XXXXX"
ws_baz.cells(1,1) = "baz"
set ws_bar = findWorksheet("bar", deleteIfExists := true)
if ws_bar.cells(1,1) <> "" then ' {
msgBox "bar: nok"
end if ' }
set ws_baz = findWorksheet("baz", deleteIfExists := false)
if ws_baz.cells(1,1) <> "baz" then ' {
msgBox "baz: nok"
end if ' }
dim sh_nothing as worksheet
set sh_nothing = collObjectOrNothing(activeWorkbook.sheets, "does not exist")
if not sh_nothing is nothing then
msgBox "expected sh_nothing to be nothing"
end if
end sub ' }
private sub test_deleteRange() ' {
dim ws_rng as worksheet
set ws_rng = findWorksheet("rng")
with ws_rng
with .range( .cells(3,2), .cells(6, 4) ) ' {
.value = "A"
.interior.color = rgb(255, 135, 40)
.name = "A"
end with ' }
with .range( .cells(6,3), .cells(7, 5) ) ' {
.value = "B"
.interior.color = rgb( 40, 180,220)
.name = "B"
end with ' }
deleteRange "A"
deleteRange "thisRangeDoesNotExist"
with .cells(6, 4)
if .text <> "" then
msgBox "test_deleteRange failed (1)"
end if
if .interior.Color <> rgb(255, 255, 255) then
msgBox "test_deleteRange failed (2)"
end if
end with
end with
end sub ' }
private sub test_hyperlink() ' {
dim ws as worksheet
set ws = findWorksheet("hyperlinks")
insertHyperlinkToVBAMacro ws.cells(2,2), "Hyperlink one" , "hyperlinked_1"
insertHyperlinkToVBAMacro ws.cells(3,2), "Hyperlink two (hello world, 42)", "hyperlinked_2", "Hello world", 42
insertHyperlinkToVBAMacro ws.cells(4,2), "Hyperlink two (foo bar baz, 99)", "hyperlinked_2", "foo bar baz", 99
end sub ' }
private sub test_num_letter_() ' {
if colLetterToNum("Z" ) <> 26 then msgBox "Expected 26"
if colLetterToNum("AA") <> 27 then msgBox "Expected 27"
if colNumToLetter( 26 ) <> "Z" then msgBox "Expected 'Z'"
if colNumToLetter( 27 ) <> "AA" then msgBox "Expected 'AA'"
end sub ' }
private sub test_num_letter() ' {
dim curRefStyle as long
curRefStyle = application.referenceStyle
application.referenceStyle = xlA1 : test_num_letter_
application.referenceStyle = xlR1C1 : test_num_letter_
application.referenceStyle = curRefStyle
end sub ' }
public function hyperlinked_1() as range ' {
msgBox "hyperlink one"
'
' The function must return the range that the
' hyperlink jumps to
'
' set hyperlinked_1 = sheets("hyperlinks").cells(2,2)
set hyperlinked_1 = selection
end function ' }
public function hyperlinked_2(txt as string, num as long) as range ' {
msgBox "hyperlink two, txt = " & txt & ", num = " & num
' msgBox "hyperlink two, txt = " & txt
set hyperlinked_2 = selection
end function ' }