# VB Excel routine not working

#### 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!

#### wayneh

Joined Sep 9, 2010
16,399
Need a capital S on showx?

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

#### 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).

#### cmartinez

Joined Jan 17, 2007
7,080
I can't believe I've spent an entire day trying to sort this out... and still... NOTHING!!!

#### 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.

#### 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
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

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...).

#### 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).

#### panic mode

Joined Oct 10, 2011
1,856
excel 2010

#### cmartinez

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

#### cmartinez

Joined Jan 17, 2007
7,080

#### 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?

#### 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:

#### 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!

#### 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.

#### wayneh

Joined Sep 9, 2010
16,399