User Defined Function Call Error

[dmelt253]

So this is related to an earlier post I submitted. Someone was very kind enough to give me a function that did exactly what I wanted. However the only way that I can call this function is by creating it as an add-in. I would much rather use this in a macro enabled workbook because then other people can utilize this Macro without installing the add-in.
I guess I just don't understand where to put the function or exactly how to call it in my sub. Here is an excerpt of the code I have since the whole Macro is Very long.

Option Explicit
Public Function AreaOwner(BinName As String) As Variant
    Dim iBins As Long
     
    AreaOwner = CVErr(xlErrRef)
     
    With Worksheets("AreaOwnerKey")
        For iBins = 2 To .Rows.Count
            With .Rows(iBins)
                If UCase(.Cells(1).Value) <= UCase(BinName) And UCase(BinName) <= UCase(.Cells(2).Value) Then
                    AreaOwner = .Cells(3).Value
                    Exit Function
                ElseIf Len(.Cells(1).Value) = 0 Then
                    Exit Function
                End If
            End With
        Next iBins
    End With
End Function
Private Sub AutomatedPickLog()
Range("E1").Select
    ActiveCell.FormulaR1C1 = "=AreaOwner(RC[-1])"
    Selection.AutoFill Destination:=Range(Range("E1"), Range("F1").End(xlDown).Offset(0, -1)), Type:=xlFillDefault
End Sub

Basically I am trying to use the Function like a formula that evaluates D1 and puts the answer E1 and then repeats this step for all other cells in column D. However when I run this Macro the function results in a #NAME? error.

Is there a better way to do this?

[Paul_Hossler]

Maybe something like this in a standard module. No 'Private' on the Sub

Option Explicit
Public Function AreaOwner(BinName As String) As Variant
    Dim iBins As Long
    
    AreaOwner = CVErr(xlErrRef)
    
    With Worksheets("BinsAndOwners")
        For iBins = 2 To .Rows.Count
            With .Rows(iBins)
                If UCase(.Cells(1).Value) <= UCase(BinName) And UCase(BinName) <= UCase(.Cells(2).Value) Then
                    AreaOwner = .Cells(3).Value
                    Exit Function
                ElseIf Len(.Cells(1).Value) = 0 Then
                    Exit Function
                End If
            End With
        Next iBins
    End With
End Function

Sub AutomatedPickLog()
    Dim rBins As Range
    
    Set rBins = ActiveSheet.Range("D1")
    Set rBins = Range(rBins, rBins.End(xlDown))
    
    Range("E1").Formula = "=AreaOwner(D1)"
    Range("E1").AutoFill Destination:=rBins.Offset(, 1)
End Sub

[dmelt253]

After hours of debugging this function the obvious finally became apparent. Correct me if I'm wrong but you cannot run a macro with a function on a workbook that is not macro enabled. So I switched things around since the report that contains the data comes out of IBM Cognos I cannot change it to a .xlsm. I can however create another spreadsheet that is Macro enabled that holds the function and the Macro that the user will run that imports the data from Cognos report. When I do it this way everything works and I am able to email this workbook to another user who can run it with out installing add-ins or having to enable the developer tab.

[Paul_Hossler]

since the report that contains the data comes out of IBM Cognos I cannot change it to a .xlsm.

A. Couldn't you

1. take the Cognos XLSX and FileSaveAs it as a XLSM
2. Use the VBE to insert a standard macro sheet
3. put the code on that?

Since the purpose of the sub AutomatedPickLog is to enter formulas using the AreaOwner function, you would need AreaOwner on the Cognos WB at least

OR

B. you could just have AutomatedPickLog put the value instead of the formula in the Cognos WB

[SamT]

Can't do A, He would have to modify the new Cognos report, then distribute it each time.

I like B better than a Formula with a UDF call, unless the user will be modifying the data in Cognos for some reason. He would still only have to distribute one book one time.

[dmelt253]

I ended up using option B which works pretty well. It actually works better this way because now I don't have to import a table from a data connection to use the function I need. I can just host in another worksheet in the same Workbook. I can also use the first worksheet to give instructions to end users that don't know how to run a Macro. I work in a warehouse so this is the case for most users.

The only unrelated but major gripe I have is how Excel uses ColumnWidth. Its not very intuitive at all! I can have 2 workbooks side by side (2 identical monitor setup) and they can have the same margin settings and same pixels for row height and column width but they won't be the same. That will be my next subject to dig into.

[SamT]

Column Width is set by the number of 0's. ie width = 29.7 is the same width as 29.7 0's are.

[Paul_Hossler]

I ended up using option B which works pretty well. It actually works better this way because now I don't have to import a table from a data connection to use the function I need. I can just host in another worksheet in the same Workbook. I can also use the first worksheet to give instructions to end users that don't know how to run a Macro. I work in a warehouse so this is the case for most users.

How about making an Add In that the user can load one time and which runs on the active workbook?

[dmelt253]

How about making an Add In that the user can load one time and which runs on the active workbook?

The add in was how I originally was using the function for my own purposes but most of the users that do order management are not very savvy in Excel. Most of their work is done within Oracle. So I tried to make it as easy as possible without having to navigate through menus and install any add ins. Now all they have to do is make sure the macro enabled workbook is open at the same time as the report from Cognos and then run the macro. They don't even have to enable the developer tab on the ribbon.

[snb]

You can copy any Addin to Excel's startup directory (or you can make a macro to do so).

[Paul_Hossler]

You can copy any Addin to Excel's startup directory (or you can make a macro to do so).

@demelt253 -- roger that

You can make the XLAM add in that has a simple ribbon button

1. Be careful with 'ThisWorkbook' and 'ActiveWorkbook
2. Probably need to add check to make sure the right WS is active, or read all WS in ActiveWorkbook looking for a ORACLE file 'signature'

Tell the users to copy it to their C:\Users\<user>\AppData\Roaming\Microsoft\Excel\XLSTART, or use a simple batch file to do it

[SamT]

K.I.S.S.