Type / Function Help

[Darren Arnold]

OK, so I'm trying to return values for an object based on it's name.

Let's say it's a brick, and I call it as Brick("S") to say it's a standard brick.

Inside the function it determines the various dimensions of an "S" brick, I want to return those valuses without writing a function to return each individually.

So I want, from the Spreadsheet Cell to simply call it as say

=BRICK("S").LENGTH

and have it return a value in the cell.

I want to be able to do others like =BRICK("S").WIDTH and =BRICK("S").WIDTH*BRICK("S").HEIGHT

Now.. this is as far as I get:

I defined a type called Dimensions in Visual Basic:

Public Type Dimensions

Area As Double
Length As Double
Height As Double
Volume As Double
Width As Double

End Type

So far so good.

I scratched up a test function:

Function Brick() As Dimensions

Brick.Area = 0
Brick.Length = 230
Brick.Height = 0
Brick.Volumne = 0
Brick.Width = 0

End Function

If I call it as =Brick.Length in the spreadhseet it pops up the VB windows gives me:
"User defined type not defined"

If I call it as Brick().Length it says the formula I typed contains an error


Help!!!

I think logically I'm on the right track but having one of those derailments you have at 4am when you forget to factor in that sine can go negative or that some twit put a 0 where it shouldn't be and everything tried to divide by it.

From

 

[RoryA]

You can't do that in a cell (you're basically attempting to write VBA in a cell). You would have to have a wrapper function that you pass a string containing the name of the property you want back (or an index number) and you'd need a class rather than a UDT so that you can use CallByName.

 

[Darren Arnold]

Hi Rory,

I did all the coding in Visual Basic

Now all I want to do is in the spreadsheet cell call it like:

=BRICK("S").length

Instead of having to write a function that calls Brick and returning a value from the second function.

 

[Darren Arnold]

I should explain, although figuring out Types and getting into visual basic makes me Mr Fancypants at work, I'm not actually very good or understand much of it. What I'm trying to do is refine equations for estimating so I don't have to type complex unreadable formulas like IF(A1=B1,Pi()*(A1/2)^2)),IF(A1=C1.......... you get the drift.

Is there any way to say =a(1).b in a cell and get a formula that way and if so, how, pretty much precisley, because I'm an idiot

 

[RoryA]

I know, but you can't do that. You're effectively trying to refer to an object in a cell and that won't work.

 

[RoryA]

At the most simple level:
1. Add a class module and rename it Dimensions.
in its code module add:

Public Area As Double
Public Length As Double
Public Height As Double
Public Volume As Double
Public Width As Double

2. Remove your User-defined Type.
3. Change the function code to:

Function Brick(strType As String)
   Dim objDim As Dimensions
   Set objDim = New Dimensions
   With objDim
      .Area = 0
      .Length = 230
      .Height = 0
      .Volume = 0
      .Width = 0
   End With
   Brick = CallByName(objDim, strType, VbGet)
End Function

4. Change the cell call to:
=Brick("Length")

 

[Darren Arnold]

OK, Thanks, Rory, back to when I started then which was individual function names. I think the medium I'm going to have to go for is


=BrickDimension("Type","Length") and code the rest to return a value based on the parameters. I think that's how the CELL function works.

I do appreciate your swift answer, probably saved me 12 hours of swearing in the middle of the night determined that it MUST be possible.

 

[Darren Arnold]

OK, I kind of see that, Rory, I'll wrap my head around it. Instead of doing a whole bunch of case statements to figure out what is being done, that will allow me just to name it and let it find itself.

Awesome.

As soon as I unfry my brain and understand it, I'll probably do as you suggested. Thanks again.

 

[Darren Arnold]

I know, but you can't do that. You're effectively trying to refer to an object in a cell and that won't work.

OK that worked for me, less elegant than I liked but I don't own a gold plated toilet either.

I really appreciate your help - here's what I got:

Public Area As Double
Public Depth As Double
Public Length As Double
Public Height As Double
Public Volume As Double
Public Width As Double

Function Brick(BrickType, BrickDimension As String)

Dim BrickDimensions As Dimensions
Set BrickDimensions = New Dimensions
With BrickDimensions
.Area = 0
.Depth = 0
.Length = 0
.Height = 0
.Volume = 0
.Width = 0
End With

Select Case UCase(BrickType)
Case "ST", "SD", "STD", "STA", "STAN", "STANDARD"
With BrickDimensions
.Area = 0.01748
.Depth = 0
.Length = 110
.Height = 76
.Volume = 0.0019228
.Width = 230
End With
Case "MA", "MB", "MAX", "MXI", "MAXI", "MAXIBRICK"
With BrickDimensions
.Area = 0.04941
.Depth = 0
.Length = 90
.Height = 162
.Volume = 0.004469
.Width = 305
End With
Case "LO", "LR", "LON", "LOR", "LONG", "LONGREACH"
With BrickDimensions
.Area = 0.02318
.Depth = 0
.Length = 90
.Height = 76
.Volume = 0.0020862
.Width = 305
End With
Case Else
With BrickDimensions
.Area = 0
.Depth = 0
.Length = 0
.Height = 0
.Volume = 0
.Width = 0
End With
End Select

Brick = CallByName(BrickDimensions, BrickDimension, VbGet)

End Function

Apart from the dangerous BrickDimension/BrickDimensions names being close, it otherwise does what I want.

Thanks again.