A simple function

  • Thread starter Thread starter Bob Grossman
  • Start date Start date
B

Bob Grossman

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.
 
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.
 
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
 
That error indicates that Excel can't find the function. Is it in the same workbook? If so,
write it as =func(a1,b1)
 
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.
 
Back
Top