Function working within Sub but not in spreadsheet

[falcoso]

I am currently working on a function for a spreadsheet that I am developing. it sorts a number of smaller events into groups that are categorized by a single integer (e.g. 1-15) and in some cases further defined by extra letters.

The purpose of the function is to sum the frequencies of all the events within a certain group e.g. search through all the faults and if it conatins '2' be it within in 'A2P' or someother similar string it will add tehm to the current total.

The function itself works when I call it in a sub for specific cell references that are within the spreadsheet, but when I use the funciton specifically in the spreadsheet with the exact same references it only returns #VALUE

Here is the code for the function:

Public Function sumfreq(ldbf1, ldbflist, freqlist) As Double
Dim total As Double
total = 0
For i = 1 To UBound(freqlist)
If onlyDigits(Left(ldbflist(i, 1), 3)) = ldbf1 Then
total = total + freqlist(i, 1)
End If
Next i
sumfreq = total
End Function

The onlydigits function works within the spreadsheet itself anyway so that is not the issue, but the code for that is:

Function onlyDigits(s As String) As String
Dim retval As String ' This is the return string. '
Dim i As Integer ' Counter for character position. '


' Initialise return string to empty '
retval = ""


' For every character in input string, copy digits to '
' return string. '
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next


' Then return the return string. '
onlyDigits = retval
End Function

I'm fairly new to coding so it may be a simple explanatio, but since the function only returns a single value I'm confused as to why this works in a sub (with the correct value) but not in the spreadsheet itself.

Thanks

[Leith Ross]

Hello falcoso,

The arguments used in your function call sumfreq appear to be arrays you have defined elsewhere in your VBA project. When using a UDF (User Defined Function) Excel expects the arguments to passed from the worksheet and not from the VBA project.

[SamT]

A syntax error here: VBA can deal with it but it requires extra CPU cycles.

If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
Should be

If IsNumeric Mid(s, CStr(i), 1) Then 'The second check for <=9 is redundant as i is always a single character.
retval = retval & Mid(s, i, 1)


I am not sure as to the logic of your code, but to get it to work as a worksheet function:

Public Function sumfreq(ldbf1 As Range, ldbflist As Variant, freqlist As Variant)
Dim total As Double
Dim arrfreq as Variant
Dim arrldbf As Variant

arrfrfeq =freqlist
arrldbf = ldbflist

For i = 1 To UBound(arrfreq)
If onlyDigits(Left(arrldbf(i, 1), 3)) = ldbf1 Then
total = total + freqlist(i, 1)
End If
Next i
sumfreq = total
End Function

If that doesn't work, you can write an interface for the worksheet

Public Function sumFreqInterface(ldbf1 As Range, ldbfList as Range, freqlist as Range) As double
Dim arrLdbf As Variant
Dim arrFreq As Variant

arrfrfeq =freqlist
arrldbf = ldbflist

sumFreqInterface = sumFreq(ldbf1, arrldbf, arrFreq)
End Function

Note that, IMO, the code does not match your text. Probably means that I just don't understand one or both of them.

Comments:
ldbf1 is numerical so onlyDigits should return a Long. onlyDigits = CLng(retval)

[falcoso]

A syntax error here: VBA can deal with it but it requires extra CPU cycles.[CODE]


Ah thanks for cleaning that up, I googled the original code so always assumed it was the most efficient way of doing it.




Note that, IMO, the code does not match your text. Probably means that I just don't understand one or both of them.

Well it doesn't matter that you probably dont' understand it, you got it working for me! Thanks for all your help guys. Looks like the problem was just a matter of copying in the input ranges into an array to then be processed for the spreadsheet to read it properly.

Thank you!