Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SIMPLE CONCATENATE FUNCTION | Excel Worksheet Functions | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Very simple function not working | Excel Discussion (Misc queries) | |||
simple if then function | Excel Worksheet Functions | |||
Simple Function Help ~ Please | Excel Worksheet Functions |