VB Excel routine not working

Discussion in 'Programmer's Corner' started by cmartinez, Jul 28, 2016.

  1. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    In my excel module1 file:

    Code (Text):
    1.  
    2.   Public Type punto
    3.      x As Double
    4.      y As Double
    5.      z As Double
    6.   End Type
    7.  
    8.   Public Function abc() As punto
    9.      abc.x = 1
    10.      abc.y = 2
    11.      abc.z = 3
    12.   End Function
    13.  
    14.   Public Function ShowX(pp As punto) As Double
    15.      ShowX = pp.x
    16.   End Function
    17.  
    When I type the following formula in a cell: =showx(abc()) all I get is a #VALUE! error
    I'm expecting Excel to show a value of 1 in that cell

    Why is that? I'm desperate!
     
  2. wayneh

    Expert

    Sep 9, 2010
    12,078
    3,014
    Need a capital S on showx?

    I've done a ton of VB in Excel but this doesn't look familiar to me.
     
    cmartinez likes this.
  3. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    It's as simple as passing a user defined structure (type) from one function to the next. And unfortunately it's not about lowercase or uppercase...
    I've already tried using the CLASS definition to no avail.... I'm pulling my hair here! ...
     
  4. wayneh

    Expert

    Sep 9, 2010
    12,078
    3,014
    Is it a problem of a "1" being an integer versus 1.0 being "double"? That would cause a problem in Swift (which I'm struggling to become fluent in, to code apps in iOS).
     
    cmartinez likes this.
  5. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    I can't believe I've spent an entire day trying to sort this out... and still... NOTHING!!! :(
     
  6. wayneh

    Expert

    Sep 9, 2010
    12,078
    3,014
    About all I can suggest is to break it into small pieces to verify each step is behaving as expected. You've probably done a lot of that already. Make no assumptions.
    If you can attach an example, I'll take a look at it. But as I said, it looks foreign to me relative to previous experience.
     
    cmartinez likes this.
  7. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    Trust me, I've been extremely meticulous. It's unbelievable that a question this simple has proven to be so hard to answer.
     
  8. MrChips

    Moderator

    Oct 2, 2009
    12,404
    3,352
    I don't know anything about VB Excel so if my comments are rubbish please ignore.
    Your code makes no sense to me.
    abc should be a variable, not a function.
    You have not declared the variable abc.
    I expect to see something like:
    Dim abc as punto
     
    cmartinez likes this.
  9. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    punto is just the name of a simple structure
    abc() is a function that returns a punto structure with constant values
    ShowX extracts the x component of the given structure.



    I think I've found the answer to what's going on here:
    http://www.vertex42.com/ExcelArticles/user-defined-functions.html

    and also here are some pretty interesting comments:
    http://stackoverflow.com/questions/15659779/set-a-cell-value-from-a-function


    The most important observations of the previous articles are:

    Limitations of UDF's:
    • Cannot "record" an Excel UDF like you can an Excel macro.
    • More limited than regular VBA macros. UDF's cannot alter the structure or format of a worksheet or cell.
    • If you call another function or macro from a UDF, the other macro is under the same limitations as the UDF.
    • Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not necessarily "macros".
    • Excel user defined functions in VBA are usually much slower than functions compiled in C++ or FORTRAN.
    • Often difficult to track errors.
    • If you create an add-in containing your UDF's, you may forget that you have used a custom function, making the file less sharable.
    • Adding user defined functions to your workbook will trigger the "macro" flag (a security issue: Tools > Macros > Security...).
     
  10. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    And here's another guy that has been suffering the same problem I have.
    One of the suggestions mentioned was that he should just ditch Excel and use a software called Resolver One instead!
     
  11. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
  12. panic mode

    Senior Member

    Oct 10, 2011
    1,318
    304
    what version of excel you have? code is fine but what is supposed to execute it? you need event... in fact read what wellsr replied to ravi. that could work in older excel (before 2010).
     
    cmartinez likes this.
  13. panic mode

    Senior Member

    Oct 10, 2011
    1,318
    304
    excel 2010 clickme.jpg
     
    cmartinez likes this.
  14. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    That is very, very interesting. Thank you.
    It seems that, for it to work, the function call should be made from the spreadsheet code, and not directly from the cell. I'm working with Office 2007.

    I'm still trying to figure things out here.
     
  15. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    Question, how do you add a button to a spreadsheet?
     
  16. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    How, for instance, do I make this function run in a spreadsheet?

    Code (Text):
    1.  
    2. Sub FillRange2()
    3.   Num = 1
    4.   For Row = 0 To 9
    5.    For Col = 0 To 9
    6.     Sheets("Sheet1").Range("A1").Offset(Row,Col).Value = Num
    7.     Num = Num + 1
    8.    Next Col
    9.   Next Row
    10. End Sub
    11.  
    Should it be activated by a button or some sort of event?
     
  17. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    Alright... this is EXACTLY what has me baffeled and with a pain in the small of my back:

    Code (Text):
    1.  
    2.   Public Type punto
    3.    x As Double
    4.    y As Double
    5.    z As Double
    6.   End Type
    7.  
    8.   'Convert a range to a point structure
    9.   Public Function RtP(r As Range) As punto
    10.    RtP.x = r(1, 1)
    11.    RtP.y = r(1, 2)
    12.    RtP.z = r(1, 3)
    13.   End Function
    14.  
    15.  
    16.   Public Function DistanceX(a As Range, b As Range) As Double
    17.    DistanceX = Sqr((b(1, 1) - a(1, 1)) ^ 2 + (b(1, 2) - a(1, 2)) ^ 2 + (b(1, 3) - a(1, 3)) ^ 2)
    18.   End Function
    19.  
    20.   Public Function Distance(a As punto, b As punto) As Double
    21.    Distance = Sqr((b.x - a.x) ^ 2 + (b.y - a.y) ^ 2 + (b.z - a.z) ^ 2)
    22.   End Function
    23.  
    If I type in a cell the function =DistanceX(U12:W12,U13:W13), things work perfectly ok.
    But if I try to use =Distance(rtp(U12:W12),rtp(U13:W13)) instead, I get a #VALUE! error in that cell

    What gives?
     
    Last edited: Jul 30, 2016
  18. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    Here's an update. If I use the function:

    Code (Text):
    1.  
    2.   Public Function DistanceY(aa As Range, bb As Range) As Double
    3.    Dim aa As punto, bb As punto
    4.     a = RtP(aa)
    5.     b = RtP(bb)
    6.  
    7.     DistanceY = Sqr((b.x - a.x) ^ 2 + (b.y - a.y) ^ 2 + (b.z - a.z) ^ 2)
    8.   End Function
    9.  
    10.  

    With the previous punto type declaration, then everything works a-Ok!
     
  19. cmartinez

    Thread Starter AAC Fanatic!

    Jan 17, 2007
    3,550
    2,478
    So, what I've figured, is this:

    In a worksheet cell, you cannot define the initial parameters of a UDF using another UDF. (such as writing distance(rtp(U12:W12),rtp(U13:W13)) in the target cell, see post #1) Those parameters must be standard VBa type variables (such as range, cell location, math functions, etc...) But what you're allowed to do, is use those parameters from inside the function to define UDV (user defined variables, or types. See the Type Punto structure in post #17) and then call other UDFs that accept them as parameters. See my last post, as an example.

    This does place some limitations on what can be done, but at least it does not represent an insurmountable problem.

    A very special thanks to @wayneh, @MrChips and @panic mode (that example that you posted was essential for my understanding) for taking the time to respond to this thread.

    If anyone feels like contributing additional knowledge on this subject, then by all means, please feel free to do so.
     
  20. wayneh

    Expert

    Sep 9, 2010
    12,078
    3,014
    Do you still need an answer to this one? It's one of those rare questions where I actually know the answer.
     
Loading...