falcoso
05-05-2016, 06:10 AM
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
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