Public Functions

[Guest]

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

 

[Guest]

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

Does that help?

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

XL2002, WinXP-Pro

 

[Guest]

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.

 

[Guest]

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