I'm trying to create a custom function in excel 2007 using visual basic. The
parameter type seems to default to numeric and I cannot figure out the syntax
to specify text parameters. The example custom function in help only uses
text parameters. I've gotten my function to compile, but using it results in
a #VALUE!.

Text parameters are STRING variables
e.g.

DIM mytext as string

HTH

"Jocken" wrote:

I'm trying to create a custom function in excel 2007 using visual basic. The
parameter type seems to default to numeric and I cannot figure out the syntax
to specify text parameters. The example custom function in help only uses
text parameters. I've gotten my function to compile, but using it results in
a #VALUE!.

So I tried that like so:

Function CONTAINS(within, test)

Dim within As String
Dim test As String

the compiler complains about a duplicate declaration.
what I want to say is "Function CONTAINS(String within, String test)"; All I
need is for VBA to set up a function that expects two String-type parameters
and does calculations with them.

I miss Java... *tear

"Toppers" wrote:

Text parameters are STRING variables
e.g.

DIM mytext as string

HTH

"Jocken" wrote:

I'm trying to create a custom function in excel 2007 using visual basic. The
parameter type seems to default to numeric and I cannot figure out the syntax
to specify text parameters. The example custom function in help only uses
text parameters. I've gotten my function to compile, but using it results in
a #VALUE!.

Jocken wrote...
So I tried that like so:

Function CONTAINS(within, test)

Dim within As String
Dim test As String

the compiler complains about a duplicate declaration.
....

Online help is your friend. Using it, you can find answers to problems
that could take days of back & forth with newsgroups postings.

Searching VBA help for the term Function does produce a lot of chaff,
but also the topic titled 'Function Statement'. The Example link in
that topic shows several examples, the first of which is:


Function CalculateSquareRoot(NumberArg As Double) As Double
If NumberArg < 0 Then ' Evaluate argument.
Exit Function ' Exit to calling procedure.
Else
CalculateSquareRoot = Sqr(NumberArg) ' Return square root.
End If
End Function


which shows that the proper syntax requires putting the type
declaration in the function's argument list.

what I want to say is "Function CONTAINS(String within,
String test)"; All I need is for VBA to set up a function that
expects two String-type parameters and does calculations with them.

And the online help examples show that your syntax is wrong. There are
many examples of proper syntax in online help. In MSFT BASIC dialects,
types are specified by APPENDING 'As' followed by the type name AFTER
the variable name.

I miss Java... *tear
....

Most definitely VB* isn't Java, but you can't expect any language that
doesn't trace its ancestry to C to use C-like syntax. At some point
you have to sneak a peak at the language docs.

Function CONTAINS(within as String, test as String)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jocken" wrote in message ...
| So I tried that like so:
|
| Function CONTAINS(within, test)
|
| Dim within As String
| Dim test As String
|
| the compiler complains about a duplicate declaration.
| what I want to say is "Function CONTAINS(String within, String test)"; All I
| need is for VBA to set up a function that expects two String-type parameters
| and does calculations with them.
|
| I miss Java... *tear
|
| "Toppers" wrote:
|
| Text parameters are STRING variables
| e.g.
|
| DIM mytext as string
|
| HTH
|
| "Jocken" wrote:
|
| I'm trying to create a custom function in excel 2007 using visual basic. The
| parameter type seems to default to numeric and I cannot figure out the syntax
| to specify text parameters. The example custom function in help only uses
| text parameters. I've gotten my function to compile, but using it results in
| a #VALUE!.