VB Excel routine not working

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
In my excel module1 file:

Code:
  Public Type punto
     x As Double
     y As Double
     z As Double
  End Type

  Public Function abc() As punto
     abc.x = 1
     abc.y = 2
     abc.z = 3
  End Function
 
  Public Function ShowX(pp As punto) As Double
     ShowX = pp.x
  End Function
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!
 

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
Need a capital S on showx?

I've done a ton of VB in Excel but this doesn't look familiar to me.
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! ...
 

wayneh

Joined Sep 9, 2010
16,399
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).
 

wayneh

Joined Sep 9, 2010
16,399
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.
 

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
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.
Trust me, I've been extremely meticulous. It's unbelievable that a question this simple has proven to be so hard to answer.
 

MrChips

Joined Oct 2, 2009
21,818
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
 

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
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...).
 

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
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!
 

panic mode

Joined Oct 10, 2011
1,856
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).
 

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
How, for instance, do I make this function run in a spreadsheet?

Code:
Sub FillRange2()
  Num = 1
  For Row = 0 To 9
   For Col = 0 To 9
    Sheets("Sheet1").Range("A1").Offset(Row,Col).Value = Num
    Num = Num + 1
   Next Col
  Next Row
End Sub
Should it be activated by a button or some sort of event?
 

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
Alright... this is EXACTLY what has me baffeled and with a pain in the small of my back:

Code:
  Public Type punto
   x As Double
   y As Double
   z As Double
  End Type

  'Convert a range to a point structure
  Public Function RtP(r As Range) As punto
   RtP.x = r(1, 1)
   RtP.y = r(1, 2)
   RtP.z = r(1, 3)
  End Function


  Public Function DistanceX(a As Range, b As Range) As Double
   DistanceX = Sqr((b(1, 1) - a(1, 1)) ^ 2 + (b(1, 2) - a(1, 2)) ^ 2 + (b(1, 3) - a(1, 3)) ^ 2)
  End Function

  Public Function Distance(a As punto, b As punto) As Double
   Distance = Sqr((b.x - a.x) ^ 2 + (b.y - a.y) ^ 2 + (b.z - a.z) ^ 2)
  End Function
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:

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
Here's an update. If I use the function:

Code:
  Public Function DistanceY(aa As Range, bb As Range) As Double
   Dim aa As punto, bb As punto
    a = RtP(aa)
    b = RtP(bb)
 
    DistanceY = Sqr((b.x - a.x) ^ 2 + (b.y - a.y) ^ 2 + (b.z - a.z) ^ 2)
  End Function

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

Thread Starter

cmartinez

Joined Jan 17, 2007
7,080
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.
 
Top