Public Functions

[Sprinks]

To simplify unit conversions, I created a series of functions like the
following, intended to be used either by typing the function directly in the
cell

=8*LPS2CFM()

or by highlighting the cell(s), and selecting it from a custom menu, which
executes the subroutine below.

The code defining them is in a workbook called Macros.xls, which loads from
the XLStart folder.

They run as expected when tested on the Macros worksheet, but generate a
#Name# error from anywhere else. Can anyone tell me how I can gain access to
them?

Thanks.

Sprinks



Public Function LPS2CFM()
LPS2CFM = 2.118880003
End Function

Public Sub SubLPS2CFM()
Dim Cell As Range
Dim strWS As String
For Each Cell In Selection
If IsNull(Cell.Formula) Or Cell.Formula = "" Then
Else

‘strip equal sign, rounding, and unnecessary parentheses
strWS = fxnWorkingString(Cell.Formula)

Cell.Formula = "=" & strWS & "*LPS2CFM()"

End If
Next Cell
End Sub

[Ron Coderre]

Did you put the VBA code in a general module and NOT a sheet module?

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Sprinks" wrote:

> To simplify unit conversions, I created a series of functions like the
> following, intended to be used either by typing the function directly in the
> cell
>
> =8*LPS2CFM()
>
> or by highlighting the cell(s), and selecting it from a custom menu, which
> executes the subroutine below.
>
> The code defining them is in a workbook called Macros.xls, which loads from
> the XLStart folder.
>
> They run as expected when tested on the Macros worksheet, but generate a
> #Name# error from anywhere else. Can anyone tell me how I can gain access to
> them?
>
> Thanks.
>
> Sprinks
>
>
>
> Public Function LPS2CFM()
> LPS2CFM = 2.118880003
> End Function
>
> Public Sub SubLPS2CFM()
> Dim Cell As Range
> Dim strWS As String
> For Each Cell In Selection
> If IsNull(Cell.Formula) Or Cell.Formula = "" Then
> Else
>
> ‘strip equal sign, rounding, and unnecessary parentheses
> strWS = fxnWorkingString(Cell.Formula)
>
> Cell.Formula = "=" & strWS & "*LPS2CFM()"
>
> End If
> Next Cell
> End Sub
>

[Chip Pearson]

You need to put the workbook name in the call to the function:

=8*Macros.xls!LPS2CFM()

If you make your Macros.xls file an add-in, you don't need to
include the workbook name in the function call.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sprinks" <[email protected]> wrote in message
news:[email protected]...
> To simplify unit conversions, I created a series of functions
> like the
> following, intended to be used either by typing the function
> directly in the
> cell
>
> =8*LPS2CFM()
>
> or by highlighting the cell(s), and selecting it from a custom
> menu, which
> executes the subroutine below.
>
> The code defining them is in a workbook called Macros.xls,
> which loads from
> the XLStart folder.
>
> They run as expected when tested on the Macros worksheet, but
> generate a
> #Name# error from anywhere else. Can anyone tell me how I can
> gain access to
> them?
>
> Thanks.
>
> Sprinks
>
>
>
> Public Function LPS2CFM()
> LPS2CFM = 2.118880003
> End Function
>
> Public Sub SubLPS2CFM()
> Dim Cell As Range
> Dim strWS As String
> For Each Cell In Selection
> If IsNull(Cell.Formula) Or Cell.Formula = "" Then
> Else
>
> 'strip equal sign, rounding, and unnecessary
> parentheses
> strWS = fxnWorkingString(Cell.Formula)
>
> Cell.Formula = "=" & strWS & "*LPS2CFM()"
>
> End If
> Next Cell
> End Sub
>

[Sprinks]

Hi, Ron.

Ironically, I was just reading about that potential cause in my Walkenbach's
Power Programming book when the email came in, but that's not it--they are
located in Module1.



"Ron Coderre" wrote:

> Did you put the VBA code in a general module and NOT a sheet module?
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Sprinks" wrote:
>
> > To simplify unit conversions, I created a series of functions like the
> > following, intended to be used either by typing the function directly in the
> > cell
> >
> > =8*LPS2CFM()
> >
> > or by highlighting the cell(s), and selecting it from a custom menu, which
> > executes the subroutine below.
> >
> > The code defining them is in a workbook called Macros.xls, which loads from
> > the XLStart folder.
> >
> > They run as expected when tested on the Macros worksheet, but generate a
> > #Name# error from anywhere else. Can anyone tell me how I can gain access to
> > them?
> >
> > Thanks.
> >
> > Sprinks
> >
> >
> >
> > Public Function LPS2CFM()
> > LPS2CFM = 2.118880003
> > End Function
> >
> > Public Sub SubLPS2CFM()
> > Dim Cell As Range
> > Dim strWS As String
> > For Each Cell In Selection
> > If IsNull(Cell.Formula) Or Cell.Formula = "" Then
> > Else
> >
> > ‘strip equal sign, rounding, and unnecessary parentheses
> > strWS = fxnWorkingString(Cell.Formula)
> >
> > Cell.Formula = "=" & strWS & "*LPS2CFM()"
> >
> > End If
> > Next Cell
> > End Sub
> >

[Sprinks]

Chip,

Thank you. I have made the new functions an add-in as you suggest; works
perfectly. I also enjoy the integration into the fx dialog.

Thanks again.

Sprinks

"Chip Pearson" wrote:

> You need to put the workbook name in the call to the function:
>
> =8*Macros.xls!LPS2CFM()
>
> If you make your Macros.xls file an add-in, you don't need to
> include the workbook name in the function call.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Sprinks" <[email protected]> wrote in message
> news:[email protected]...
> > To simplify unit conversions, I created a series of functions
> > like the
> > following, intended to be used either by typing the function
> > directly in the
> > cell
> >
> > =8*LPS2CFM()
> >
> > or by highlighting the cell(s), and selecting it from a custom
> > menu, which
> > executes the subroutine below.
> >
> > The code defining them is in a workbook called Macros.xls,
> > which loads from
> > the XLStart folder.
> >
> > They run as expected when tested on the Macros worksheet, but
> > generate a
> > #Name# error from anywhere else. Can anyone tell me how I can
> > gain access to
> > them?
> >
> > Thanks.
> >
> > Sprinks
> >
> >
> >
> > Public Function LPS2CFM()
> > LPS2CFM = 2.118880003
> > End Function
> >
> > Public Sub SubLPS2CFM()
> > Dim Cell As Range
> > Dim strWS As String
> > For Each Cell In Selection
> > If IsNull(Cell.Formula) Or Cell.Formula = "" Then
> > Else
> >
> > 'strip equal sign, rounding, and unnecessary
> > parentheses
> > strWS = fxnWorkingString(Cell.Formula)
> >
> > Cell.Formula = "=" & strWS & "*LPS2CFM()"
> >
> > End If
> > Next Cell
> > End Sub
> >
>
>
>