A simple function
 

It has been 8 or so years since I did anything in excel, please excuse
the simple question.

In cells a1 and b1 I have a string stored. In cell c1 I would like to
have a function I have written ( = Module1.func(a1,b1) ).

Function func will return a value to cell c1.

I must be doing something stupid because excel flags the cell with a
#NAME? error.

Can someone explain how to make this work?

Thanks in advance, Bob.

A simple function
 

Public Function func(range1, range2)
Dim r1, r2 As String
r1 = range1.Value
r2 = range2.Value
func =r1+r2
End Function

Put the code in a general module, not in the Sheet module.

you get a general module by doing insert=Module in the VBE with your
workbook the active project in the VBE.

--
Regards,
Tom Ogilvy


Bob Grossman wrote in message
...
My appologies.

The code is:

Public Function func(range1, range2)
Dim r1, r2 As String
r1 = range1.Value
r2 = range2.Value
MsgBox (r1+r2)
End Function

Obviously, I have some enhancements in mind once I figure out how to
make this simple example work.

Bob.

On Fri, 29 Aug 2003 22:58:49 +0100, "Phobos"
wrote:

It would help if you posted the code.

P


"Bob Grossman" wrote in message
.. .
It has been 8 or so years since I did anything in excel, please excuse
the simple question.

In cells a1 and b1 I have a string stored. In cell c1 I would like to
have a function I have written ( = Module1.func(a1,b1) ).

Function func will return a value to cell c1.

I must be doing something stupid because excel flags the cell with a
#NAME? error.

Can someone explain how to make this work?

Thanks in advance, Bob.

A simple function
 

Tom,
Thanks for the help. It worked like a charm.

Bob.

On Fri, 29 Aug 2003 19:56:27 -0400, "Tom Ogilvy"
wrote:

Public Function func(range1, range2)
Dim r1, r2 As String
r1 = range1.Value
r2 = range2.Value
func =r1+r2
End Function

Put the code in a general module, not in the Sheet module.

you get a general module by doing insert=Module in the VBE with your
workbook the active project in the VBE.

A simple function
 

Some comments about variable types and type coercion:

Public Function func(range1, range2) 'As return type? (is Variant if
omitted)

Only the second of these will be a String
Dim r1, r2 As String
'the first will be Variant.
'Use Dim r1 As String, r2 As String to make both Strings.

'Assigning Doubles to String variables? Hmmm...
r1 = range1.Value
r2 = range2.Value

'Adding Strings?
func = r1 + r2

'Careful.
'VB will try to help by coercing variable types,
'but may concatenate strings instead of adding doubles.
func = CDbl(r1) + CDbl(r2)

End Function


Compare these:
Option Explicit

Public Function funcS(range1 As Range, range2 As Range) As String
Dim r1 As String, r2 As String
r1 = range1.Text
r2 = range2.Text
funcS = r1 + r2
End Function

Public Function funcD(range1 As Range, range2 As Range) As Double
Dim r1 As Double, r2 As Double
r1 = range1.Value
r2 = range2.Value
funcD = r1 + r2
End Function

Public Function funcD2(range1 As Range, range2 As Range) As Double
Dim r1 As String, r2 As String
r1 = range1.Value
r2 = range2.Value
funcD2 = r1 + r2
End Function

Public Function funcD3(range1 As Range, range2 As Range) As Double
funcD3 = range1.Value + range2.Value
End Function

--
Bob Kilmer


"Tom Ogilvy" wrote in message
...
Public Function func(range1, range2)
Dim r1, r2 As String
r1 = range1.Value
r2 = range2.Value
func =r1+r2
End Function

Put the code in a general module, not in the Sheet module.

you get a general module by doing insert=Module in the VBE with your
workbook the active project in the VBE.

--
Regards,
Tom Ogilvy


Bob Grossman wrote in message
...
My appologies.

The code is:

Public Function func(range1, range2)
Dim r1, r2 As String
r1 = range1.Value
r2 = range2.Value
MsgBox (r1+r2)
End Function

Obviously, I have some enhancements in mind once I figure out how to
make this simple example work.

Bob.

On Fri, 29 Aug 2003 22:58:49 +0100, "Phobos"
wrote:

It would help if you posted the code.

P


"Bob Grossman" wrote in message
.. .
It has been 8 or so years since I did anything in excel, please
excuse
the simple question.

In cells a1 and b1 I have a string stored. In cell c1 I would like
to
have a function I have written ( = Module1.func(a1,b1) ).

Function func will return a value to cell c1.

I must be doing something stupid because excel flags the cell with a
#NAME? error.

Can someone explain how to make this work?

Thanks in advance, Bob.

A simple function
 

That error indicates that Excel can't find the function. Is it in the same workbook? If so,
write it as =func(a1,b1)


On Fri, 29 Aug 2003 13:55:27 -0700, Bob Grossman wrote:

It has been 8 or so years since I did anything in excel, please excuse
the simple question.

In cells a1 and b1 I have a string stored. In cell c1 I would like to
have a function I have written ( = Module1.func(a1,b1) ).

Function func will return a value to cell c1.

I must be doing something stupid because excel flags the cell with a
#NAME? error.

Can someone explain how to make this work?

Thanks in advance, Bob.

A simple function
 

Bob,

I was vaguely aware of some of this. My intent, once I was able to
see my function was to do something a lot more complicated starting
with declaring the data types to be integer.

I appreciate the help.

By the way, once I do get this function written - I will probably come
back to this group to ask how to make it available to all of the
worksheets written by my workgroup. I'll provide details on our
environment at that time.

Thanks again to all!

Bob.

On Fri, 29 Aug 2003 20:29:39 -0400, "Bob Kilmer"
wrote:

Some comments about variable types and type coercion:

Public Function func(range1, range2) 'As return type? (is Variant if
omitted)

Only the second of these will be a String
Dim r1, r2 As String
'the first will be Variant.
'Use Dim r1 As String, r2 As String to make both Strings.

'Assigning Doubles to String variables? Hmmm...
r1 = range1.Value
r2 = range2.Value

'Adding Strings?
func = r1 + r2

'Careful.
'VB will try to help by coercing variable types,
'but may concatenate strings instead of adding doubles.
func = CDbl(r1) + CDbl(r2)

End Function


Compare these:
Option Explicit

Public Function funcS(range1 As Range, range2 As Range) As String
Dim r1 As String, r2 As String
r1 = range1.Text
r2 = range2.Text
funcS = r1 + r2
End Function

Public Function funcD(range1 As Range, range2 As Range) As Double
Dim r1 As Double, r2 As Double
r1 = range1.Value
r2 = range2.Value
funcD = r1 + r2
End Function

Public Function funcD2(range1 As Range, range2 As Range) As Double
Dim r1 As String, r2 As String
r1 = range1.Value
r2 = range2.Value
funcD2 = r1 + r2
End Function

Public Function funcD3(range1 As Range, range2 As Range) As Double
funcD3 = range1.Value + range2.Value
End Function