Excel 2013 - Getting "Sub of Function Not Defined"

[8gsyikpd8]

pic1.PNGpic2.PNG

Hi - Refer to attachments

have a xlam with reusable custom functions.
workbook A calls a function in xlam
getting Sub or function not defined

Macro security looks good. Have tried putting same reusable functions in personal.xlsb - but get same results

Thanks

[shg]

1. Did you install the add-in, as opposed to just having it open?

2. For Personal, you either need to add a reference to its VBA project in the VBE, or include the name in the function, e.g.,

=personal.xlsb!domsg()

[8gsyikpd8]

Thanks shg - got the desired results by saving the udf's in an .xlsm then adding the .xlsm as a reference

[shg]

That's fine, but using an add-in is the more standard approach, and what add-ins were designed to do.

[8gsyikpd8]

So I must be missing something.

Saved as .xla in C:\Users\Kevin\AppData\Roaming\Microsoft\Addins\MyCustomFunction.xla
From Excel ribbon > Developer > addins > selected mycustomfunctions

pic1.PNGpic2.PNGpic3.PNG

[shg]

Right. Then close it, and do Excel Options > Add-Ins, browse to the directory, and install it.

[8gsyikpd8]

see attachment - its installed

pic4.PNG

[shg]

Try renaming the module something different than the workbook name.

If that doesn't work, post the add-in.

[8gsyikpd8]

Renamed mod to MCF. Same same.
I'm new at this shg. I'm not seeing option to upload file from "Reply to Thread" so tried to upload the .xlam in a new post and told me an invalid file. Here are the contents of the MCF mod in the xlam

Public Function lastRowCol(theWB As String, theWS As String) As Variant
'
' this function will return the last row number, the last column number, the last column
' letter, and the address of the last cell as an array. caller simply passess the workbook name and
' worksheet name
'
' returned values
' last row number - last row number in the worksheet. Set to 1 if no data. 0 if not a valid worksheet
' last col number - last col number in the worksheet. Set to 1 if no data. 0 if not a valid worksheet
' last col letter - last col letter in the worksheet. Set to "A" if no data. Null if not a valid worksheet
' last cell address - the fully qualified address of the last cell. Set to $A$1 if no data. Set to null if worksheet does not exist
'
Dim lastRowNumber As Integer, lastColNumber As Integer, lastColLetter As String, lastCellAddress As String
Dim wb As Workbook, wks As Worksheet, wbc As Single, wbs As Single, saveWB As String, saveWS As String
'
saveWB = ActiveWorkbook.Name
saveWS = ActiveSheet.Name
'
For wbc = 1 To Workbooks.Count
If LCase(Workbooks(wbc).Name) = LCase(theWB) Then
For wbs = 1 To Workbooks(wbc).Sheets.Count
If LCase(Workbooks(wbc).Sheets(wbs).Name) = LCase(theWS) Then
lastCellAddress = "$A$1"
Exit For
End If
Next wbs
End If
Next wbc
'
If lastCellAddress = "" Then
GoTo alldone
End If
'
Workbooks(theWB).Activate
Sheets(theWS).Activate
lastRowNumber = 1
lastColNumber = 1
lastColLetter = "A"
On Error GoTo alldone
lastRowNumber = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastColNumber = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lastCellAddress = Range(Cells(lastRowNumber, lastColNumber), Cells(lastRowNumber, lastColNumber)).Address
If lastColNumber < 26 Then
lastColLetter = Chr(64 + lastColNumber)
Else
lastColLetter = Chr(Int(lastColNumber / 26) + 64) & Chr((lastColNumber Mod 26) + 64)
End If
'
'
alldone:
Workbooks(saveWB).Activate
Sheets(saveWS).Select
lastRowCol = Application.Transpose(Array(lastRowNumber, lastColNumber, lastColLetter, lastCellAddress))
End Function

[shg]

Zip the file and post it.