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

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" wrote in message
...
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

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

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

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" wrote in message
...
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