6

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

Foad S. Farimani
  • 12,396
  • 15
  • 78
  • 193
Michael Molter
  • 1,296
  • 2
  • 14
  • 37
  • 1
    Judging by [this](http://www.pcreview.co.uk/threads/user-defined-types-and-overloaded-operators.3318580/), what you are seeking is not possible, though I feel it would be an excellent vba feature should it ever come to be. – Matt Cremeens Aug 17 '15 at 20:52
  • I actually bumped into that page a while ago, and was hoping things had changed since then. I'm not an expert on this sort of thing, but I believe Microsoft has expanded Office VBA recently from being VB6 based to .NET based (really don't quote me on that)? – Michael Molter Aug 17 '15 at 20:56
  • 1
    One thought would be to create a [Worksheet_Change](https://msdn.microsoft.com/en-us/library/office/ff839775.aspx) event, tokenize the input of a cell, say by using the `Split` function, and if a `+` is found, modify the cell to do what you want. – Matt Cremeens Aug 17 '15 at 20:56
  • @MichaelMolter, Ms didn't do a good thing in VBA in last decade. VSTO is a dead horse. – cyboashu Aug 17 '15 at 20:58
  • I believe that @MattCremeens comment brings up a viable solution--although via an alternate route. I can imagine writing the function in a cell as I please with the overloaded '+', '-', ect. operators, and then simply having a macro expand that function into the format it has to be to work. – Michael Molter Aug 17 '15 at 21:09
  • I asked a very similar question [here](https://stackoverflow.com/q/65008505/4999991) – Foad S. Farimani Nov 26 '20 at 19:43

2 Answers2

3

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.

cyboashu
  • 10,196
  • 2
  • 27
  • 46
  • 2
    Saying _code-cosmetics_ are useless is quite the over-statement. I work in a field that demands clean, readable, debugable worksheets. Everything I spreadsheet up is checked by a instructor/supervisor, and **has** to be comprehensible to them. That's why this has been such an issue. – Michael Molter Aug 17 '15 at 21:06
  • @MichaelMolter you misunderstood code-cosmetics with code readability. I have doAdd (param1, param2) to add two numbers that's quite readable and standard. No trying to replace doAdd with '+' is cosmetics and useless overhead. – cyboashu Aug 17 '15 at 22:00
  • Not really an overhead as such, as most code cosmetic stuff occur at compile time... Note: the real way to implement this feature would be to hack the VBE – Sancarn Jan 20 '19 at 02:18
2

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
Matt Cremeens
  • 4,951
  • 7
  • 38
  • 67
  • Unfortunately, this solution, as it stands, means you could never actually simply add two numbers on the sheet. Maybe that's not a big deal for your purposes, though. – Matt Cremeens Aug 17 '15 at 21:11