Operator Overloading in Excel VBA

Question

What I would like to do:

I would like to use operator overloading in Excel to run custom functions on my custom data types. For example, when evaluating a formula, I want Excel to run my function instead of the '+' operator when the calculation involves one of my custom data types.

Why I want to do it:

In analytical chemistry, every number has an uncertainty attached to it and is written:

13.56 (±0.02) mm

I would like to create a custom data type that keeps the magnitude and the uncertainty of the number together in the same cell.

Additionally, I want to implement operator overloading, so when I write

=A1+A2

and either A1 or A2 contains an uncertainty-type number, my custom function runs instead of the default '+' operator to calculate the uncertainty.

This would make my spreadsheets much cleaner, as currently, I have to write such a statement as

=ADD_UNC(A1, A2)

Which is fine for very simple equations, but becomes a pain when the operation I am trying to form is even slightly non-trivial.

=MULT_UNC(A3, ADD_UNC(MULT_UNC(A5, A1, A2), A3)

vs.

=A3*((A1*A2)+A3)

Why I assume this is possible:

I know in real, full-blown programming languages such as C#, operator overloading is very common and very easy to perform.

Thank you for your help,

Michael

Answer 1

Here is a hack using the Worksheet_Change event. You can essentially place in a cell that contains a "+" character anything you want, thereby effectively disengaging the "+" signs normal function.

Private Sub Worksheet_Change(ByVal Target As Range)
If UBound(Split(CStr(Target), "+")) > 0 Then
    Target = "Overloaded"
Else:
    Target = "Not overloaded"
End If
End Sub

Answer 2

Not possible in VBA. VBA was intended to provide scripts which help with automation. You see, we call them macro. VBA is not built on top of modular classes or objects. Your VBE writes direct P-code the moment you type/ hit enter in the editor. VBA is awesome and packs alot of features but expecting these kind of facilities in VBA is a bit of stretch. No possibility to have this feature even in future. and Just a suggestion, never worry too much about code-cosmetics, they are useless overhead.