ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Creating a function call from from (https://www.excelbanter.com/excel-programming/333007-creating-function-call.html)

Anders

Creating a function call from from
 
Hi all helpful experts :-).

I would like a form to produce a function call to a home made VBA function.
I don't want to call the function directly from the form since I want to be
able later to use all nice internal functions as copy cell, drag drop, extend
etc.

I have made this silly VBA function in a module:
Public Function anders_test(roof As Variant, tail As Variant) As Double
If IsNumeric(roof) And IsNumeric(tail) Then
anders_test = roof / tail
Else
anders_test = "Invalid input"
End If
End Function

I have made a form with this content under an OK button:
Private Sub cmdOK_Click()
ActiveCell.Value = "=anders_test(b6;b6)"
End Sub

When I try to run this I get the error message 1004, Application-defined or
object-defined error. If I try to save a normal value in the activecell.value
like activecell.value = "test", everything works.

Can anyone help me?

Kind regards
Anders

Fred[_22_]

Creating a function call from from
 
Anders
you have defined the function as returning a double yet if either paramenter
is not a number you try to return a string.
I suggest you define the function as returning a variant i.e.:

Public Function anders_test(roof As Variant, tail aAs Variant) as Variant

Fred


"Anders" wrote in message
...
Hi all helpful experts :-).

I would like a form to produce a function call to a home made VBA
function.
I don't want to call the function directly from the form since I want to
be
able later to use all nice internal functions as copy cell, drag drop,
extend
etc.

I have made this silly VBA function in a module:
Public Function anders_test(roof As Variant, tail As Variant) As Double
If IsNumeric(roof) And IsNumeric(tail) Then
anders_test = roof / tail
Else
anders_test = "Invalid input"
End If
End Function

I have made a form with this content under an OK button:
Private Sub cmdOK_Click()
ActiveCell.Value = "=anders_test(b6;b6)"
End Sub

When I try to run this I get the error message 1004, Application-defined
or
object-defined error. If I try to save a normal value in the
activecell.value
like activecell.value = "test", everything works.

Can anyone help me?

Kind regards
Anders





All times are GMT +1. The time now is 04:06 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com