Formatting cell poulated by User Defined Function

[J. Caplan]

I have a UDF that goes off and retrieves various pieces of data from a
database based on parameters that the user enters into the UDF. Some of the
data in the DB are strings, and others are Integers or Doubles.

My UDF returns a string so that any of those values can be displayed (i.e.
ints and doubles from the DB are converted to string in my method that calls
the DB).
Public Function MyUDF(param1, param2) As String

If a value coming back from the database is the number 3.51, the UDF returns
it to the calling cell as "3.51". If I try to format this in Excel and ask
it to display one decimal point, for example, it does nothing because it
treats 3.51 as a string.
If I wrap the call to the UDF with =VALUE, that works, but I wanted to avoid
having the user have to do this. I also wanted to avoid having seperate UDFs
for each return type (i.e. one that returns String, one that returns
Integer, etc.)

Does anyone have any suggestions?

 

[Barb Reinhardt]

Have you tried

Public Function MyUDF(param1, param2) As Variant

I'm not sure it would work, but it's the next thing I'd try.

 

[J. Caplan]

Thanks for the suggestion. I forgot to mention that I tried that as well.
Even though the UDF returns a Variant, the .NET DLL that I call to (another
wrinkle here) comes back as a string. Even though the return type of the UDF
is Variant, it is still a string in the Variant and it is treated as such.

 

[Barb Reinhardt]

OK, you're over my head now. Next.

 

[Ron Rosenfeld]

I have a UDF that goes off and retrieves various pieces of data from a
database based on parameters that the user enters into the UDF. Some of the
data in the DB are strings, and others are Integers or Doubles.

My UDF returns a string so that any of those values can be displayed (i.e.
ints and doubles from the DB are converted to string in my method that calls
the DB).
Public Function MyUDF(param1, param2) As String

If a value coming back from the database is the number 3.51, the UDF returns
it to the calling cell as "3.51". If I try to format this in Excel and ask
it to display one decimal point, for example, it does nothing because it
treats 3.51 as a string.
If I wrap the call to the UDF with =VALUE, that works, but I wanted to avoid
having the user have to do this. I also wanted to avoid having seperate UDFs
for each return type (i.e. one that returns String, one that returns
Integer, etc.)

Does anyone have any suggestions?

Would it work if you had your UDF return strings as strings and numbers as
doubles?

Perhaps something along the line of:

===========================
Function foo(i) As Variant
If Val(i) Like i Then
foo = CDbl(i)
Else
foo = CStr(i)
End If
End Function
======================

At least in Excel 2007, this will return number values as numbers, and strings
as strings. It will also return something like "3.52" as a number.
--ron

 

[J. Caplan]

Thanks for the suggestion. After playing around for a while, I found that I
can do just that. The trick is to have the .NET DLL that gets the data from
the database, return back the datatype with each piece of data so that I'll
know what to convert to on the VBA side (i.e CStr, CDbl, CDate, etc.)

 

[Ron Rosenfeld]

Thanks for the suggestion. After playing around for a while, I found that I
can do just that. The trick is to have the .NET DLL that gets the data from
the database, return back the datatype with each piece of data so that I'll
know what to convert to on the VBA side (i.e CStr, CDbl, CDate, etc.)

Glad to help. Thanks for the feedback.
--ron