Function runs in VBA but not the spreadsheet

Question

I have what I think is a relatively simple function I'm running where I'm basically trying to find how much time someone stays waiting in the queue for a call back. It runs great when I'm in the VBA tab, but when I call the function in my spreadsheet I get a #REF! error.

    Function TIQ2()

    Dim time, count, i As Integer
    Dim TIQ
    time = 0
    count = 0

    NumRows = Sheet1.Range("A2", Sheet1.Range("A2").End(xlDown)).Rows.count + 1

    For i = 2 To NumRows
        If Sheet1.Range("c" & i) = "no" Or Sheet1.Range("c" & i) = "No" Then
    
            If Sheet1.Range("d" & i) = "No" Or Sheet1.Range("d" & i) = "no" Then
            time = time + Left(Sheet1.Range("g" & i), 2)
            count = count + 1
            Debug.Print time, count
        End If
    End If
  Next

  TIQ2 = WorksheetFunction.RoundUp(time / count, 0) & " minutes"
  Debug.Print TIQ2

End Function

Problematic: you're [finding the last row the wrong way](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba), you have undeclared variables (`NumRows`), `Dim time, count, i As Integer` only declares `i` as `Integer`, the rest are `Variant`, but you should probably use `Long` instead of `Integer`. — BigBen, Apr 19 '21 at 17:30
There's also an implicit conversion in `time + Left(Sheet1.Range("g" & i), 2)` that should probably be explicit using `CLng`. Lots of implicit `.Value`s too: `Sheet1.Range("c" & i).Value`. — BigBen, Apr 19 '21 at 17:32
See all remarks above: first a tip, try not use VBA names as variables (time and count) but regarding your question: Your Function name is also a cell address: TIQ2 Change this name! — EvR, Apr 19 '21 at 17:55

score 0 · Answer 1 · answered Apr 19 '21 at 18:39

0

try changing this line. some excel function requieres this syntaxis

 TIQ2 = WorksheetFunction.RoundUp(time / count, 0) & " minutes"

'-------------------
 TIQ2 =ThisWorkbook.Application.WorksheetFunction.RoundUp(time / count, 0) & " minutes"

answered Apr 19 '21 at 18:39

Miguel Angel Cortez Rodriguez

119
3

@BigBen I feel ya brother. – Nicholas Hunter Apr 19 '21 at 21:16

score 0 · Answer 2 · answered Apr 19 '21 at 18:53

Option Explicit

'Public Function fnHelloWorld()
'Function fnTIQ()
Function TIQ2()
    MsgBox "Hello World!"
End Function

Sub Test()
    Call TIQ2
End Sub

Try this ...

The first two function declarations work OK but the third one does not. The #REF error is produced by the fact that the TIQ2 function name is also a cell reference as @EvR says.

Function runs in VBA but not the spreadsheet

2 Answers2