Skip to content

Instantly share code, notes, and snippets.

@CTimmerman
Last active February 8, 2016 13:24
Show Gist options
  • Save CTimmerman/35e7d81c265404a274dc to your computer and use it in GitHub Desktop.
Save CTimmerman/35e7d81c265404a274dc to your computer and use it in GitHub Desktop.
Excel 2010 sucks almost as bad as Libre/Open Office Calc 5, so here are some notes:
Function example(ByVal r As Range, r2) As String
' Functioning practical Excel example by Cees.Timmerman@company.
' 2016-01-19 v1.0
' Sample usage: Press Alt+F11, Tools, Macros..., "example", Create, and replace template code.
' Make A1 "1;2;3" and B2 "=example(A1;A2)" or "=example(A1,A2)" if second parameter doesn't turn green.
' B2 should now have value "03". Press Ctrl+Alt+F9 to update all function cells after editing the function.
On Error GoTo ErrorHandler
Debug.Print "When debugging, this appears in the Immediate Window. It is now " & Now()
Debug.Print "r is " & r.NumberFormat _
& " " & TypeName(r) _
& ": '" & r.Value & "'"
parts = Split(r.Value, ";")
result = Application.Text(CLng(parts(2)), "00") ' CInt overflow merely causes "#VALUE!" without error handling.
Debug.Print "Result: '" & result & "'"
example = result
Exit Function
ErrorHandler:
msg = "Error #" & Err.Number & " in " & Err.Source & ": " & Err.Description
Debug.Print msg
MsgBox msg, vbCritical
Stop ' Manually Debug, Step Into (F8) thrice to highlight the statement after the error.
Err.Clear
Resume Next
End Function
@CTimmerman
Copy link
Author

Also note this shit:

x = Sheets("B2C").Cells(1294, "D")
debug.print typename(x)
String
set x = Sheets("B2C").Cells(1294, "D")
debug.print typename(x)
Range

And the fact that filters are ignored by default.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment