Vb.net Custom sum formula not calculating when blank cell are present

Lukan

New Member
Joined
Jun 5, 2004
Messages
20
I have two problems one is if i have empty cells it will not work and second that it does not calculate on tables when using sum column5
Thanks for any help provided



Code:
 Public Shared Function TotalSumToNearest(Nums As Object) As Object
        '----------------------------------------------------------------------
        '** Demonstrates how to use an array(or range of multiple cells) as
        '   a function argument.
        '   Can be called with formulas such as =GetArray(A1:B5), GetArray(A1),
        '   or GetArray({1,2,3;4,5,6}).
        '----------------------------------------------------------------------
        Dim Sum As Double, v As Object
        On Error GoTo Handler
        If IsArray(Nums) Then
            For Each v In Nums
                'If TypeOf v.Getvalue() Is ExcelEmpty Then

                'Else
                Sum = Sum + CDbl(CInches(v) / 12)
                'End If
            Next
        Else
            Sum = CDbl(Nums)
        End If
        TotalSumToNearest = toNearest16th(Sum * 12)
        Exit Function
Handler:
        ' GetArray = CVErr(2036)  'xlErrNum = 2036
    End Function
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Lukan

New Member
Joined
Jun 5, 2004
Messages
20
No help?
The Cinches is converting a string like 12'-3 1/16" to 147.0625 inches
and the toNearest16th converts it back to a string closest to the nearest sixteen of an inch something like 12'-3 1/16"
the problem is when trying to sum the results using the conversion because if there is a blank cell I get an error
Show me some love guys
thanks again
 

LockeGarmin

Active Member
Joined
Sep 11, 2015
Messages
350
It's not so much people don't want to show you the love and more that you're question is a little out of place since it's more of a question that has to do with .Net code than Excel itself. But I'd happened to have been toying around with writing Excel functions with C# for the last couple of months so I gave a crack at it.

I got this function to work alright using the ExcelDNA library in Visual Studio, but I think it should transfer ok to what you are using to compile your code.

Code:
Public Module Module1    
    Public Function SummingVB(Values As Object)
        Dim Sum As Double

        If IsArray(Values) Then

            For Each ObjectValue As Object In Values
                Dim DoubleValue As Double = 0

                If TypeOf ObjectValue Is String Then
                    'value = CDbl(CInches(v) / 12)
                ElseIf TypeOf ObjectValue Is Double
                    DoubleValue = ObjectValue
                Else
                    Double.TryParse(ObjectValue.ToString(), DoubleValue)
                End If

                Sum += DoubleValue

            Next ObjectValue

        Else
            Double.TryParse(Values.ToString(), Sum)
        End If

        Return Sum

    End Function
End Module
 

Lukan

New Member
Joined
Jun 5, 2004
Messages
20
LockeGarmin Thanks for taking the time, I don't understand it Can you explain it to me?
is this checking for empty cell's, actually I copy and pasted the code and it only sum's numbers, I'm trying to convert a string denoting inches or better yet feet and inches 12' 3 3/16 to a double as follows =147.1875 then of course than is just for the calculation of the double then I have to turn it back to a string when I sum is completed...

Code:
 Public Shared Function TotalSum(Nums As Object) As Object
        '----------------------------------------------------------------------
        '** Demonstrates how to use an array(or range of multiple cells) as
        '   a function argument.
        '   Can be called with formulas such as =GetArray(A1:B5), GetArray(A1),
        '   or GetArray({1,2,3;4,5,6}).
        '----------------------------------------------------------------------
        Dim Sum As Double, v As Object
        On Error GoTo Handler
        If IsArray(Nums) Then
            For Each v In Nums
                'If TypeOf v.Getvalue() Is ExcelEmpty Then

                'Else
                Sum = Sum + CDbl(CInches(v) / 12)
                'End If
            Next
        Else
            Sum = CDbl(Nums)
        End If
        TotalSum = Sum
        Exit Function
Handler:
        ' GetArray = CVErr(2036)  'xlErrNum = 2036
    End Function

Actually not even the function that I'm using is calculating all as it should

Thanks for your help
 
Last edited:

LockeGarmin

Active Member
Joined
Sep 11, 2015
Messages
350

ADVERTISEMENT

The method I gave you is looping through the cells and it's converting any non-double type to a double except for a string. That's where you can apply [CInches]. Anything else that can't be parsed as a double is set to 0. Instead of returning the sum you can use [toNearest16th] as necessary but since I didn't have that function I just omitted it.
 

Lukan

New Member
Joined
Jun 5, 2004
Messages
20
Cinches Code
Code:
 <ExcelFunction(Description:="To Decimal Inches")>
    Shared Function CInches(<ExcelArgument(Name:="Inches", Description:="is the first number, to which will be added")> ByVal Text_string_containing_values_for____Feet_Inches)

        '\ These values are used to examine the input string, one character at a time
        Dim vVal As String  '\ shorter name for input string
        Dim i As Integer    '\ counter to step through each character in input string
        Dim vChar As Object '\ temporary storage of each input string character

        '\ These variables hold the values we discover for feet, inches and the
        '\ numerator and denominator of the fractional inches
        Dim iFt As Integer  '\ used to store number of feet
        Dim iIn As Integer  '\ number of inches
        Dim iNumerator As Integer   '\ numerator of fractional inches
        Dim iDenominator As Integer '\ denominator of fractional inches

        '\ In the process of discovering values for feet and inches, these variable
        '\ are used to accumulate and hold numeric values
        Dim iTemp As Integer    '\ Used to build a number as each digit is read

        '\ We want to ignore spaces, except for the very important space between
        '\ the number of inches and the numerator of the fractional inches
        '\ This variable is true if the last character processed was a space
        Dim bLastCharWasSpace As Boolean

        '\ First we assign input string to variable with shorter name
        vVal = Text_string_containing_values_for____Feet_Inches

        '\ If input string is numeric, then we don't want to convert it
        If IsNumeric(vVal) Then
            CInches = vVal
            Exit Function
        End If

        '\ Now we step through each character in input string from left to right
        iTemp = 0
        bLastCharWasSpace = False
        For i = 1 To Len(vVal)
            vChar = Mid(vVal, i, 1)

            '\ If character is a number, then we combine it with numbers before it
            '\ to get a number that we can assign to feet, inches, numerator or denominator
            If IsNumeric(vChar) Then

                '\ If this is a number and the last character was a space then
                '\ chances are, the number in iTemp is inches and we need to
                '\ start over building the numerator of fractional inches
                If bLastCharWasSpace = True And iIn = 0 Then
                    iIn = iTemp
                    iTemp = 0
                End If

                '\ As we read number from left to right, we multiply value of previous
                '\ number (if any) by 10 and add this number
                If iTemp = 0 Then
                    iTemp = vChar
                Else
                    iTemp = iTemp * 10
                    iTemp = iTemp + vChar
                End If

                '\ The number we've been buiding must be feet
            ElseIf vChar = "'" Or vChar = "f" Then
                iFt = iTemp
                iTemp = 0

                '\ The number we've been bulding must be the numerator of fraction
            ElseIf vChar = "/" Then
                iNumerator = iTemp
                iTemp = 0

                '\ The number we've been building must be inches or
                '\ the denominator of the fraction, so we check to see if
                '\ there is a numerator
            ElseIf vChar = """" Or vChar = "i" Then
                If iNumerator > 0 Then
                    iDenominator = iTemp
                    iTemp = 0
                    '\ If no numerator, then the number must be inches
                ElseIf iIn = 0 Then
                    iIn = iTemp
                    iTemp = 0
                End If
            End If

            '\ Now we set our indicator so that when we process the next
            '\ character, we will know if the last character was a space
            If vChar = " " Then
                bLastCharWasSpace = True
            Else
                bLastCharWasSpace = False
            End If
        Next i

        '\ To avoid dividing by zero if there was no numerator for fraction,
        '\ we set denominator to 1
        If iNumerator = 0 And iDenominator = 0 Then iDenominator = 1

        '\ Finally, we calculate number of decimal inches and return value
        CInches = (iFt * 12) + iIn + (iNumerator / iDenominator)
    End Function

tonearest16th Code
Code:
  Public Shared Function toNearest16th(ByVal aDouble As Double) As String

        Dim result As String = ""
        ' Dim med As Integer = 0

        Dim feet, inches, remainder As Double

        feet = aDouble \ 12

        inches = aDouble Mod 12

        remainder = inches - Int(inches)

        inches = Int(inches)


        Dim fraction As String = ""

        remainder = Int(remainder / (1 / 16)) 'remainder = Int(remainder / (1 / 64))


        Select Case remainder

            Case 1
                fraction = "1/16"

            Case 2
                fraction = "1/8"

            Case 3
                fraction = "3/16"

            Case 4
                fraction = "1/4"

            Case 5
                fraction = "5/16"

            Case 6
                fraction = "3/8"

            Case 7
                fraction = "7/16"

            Case 8
                fraction = "1/2"

            Case 9
                fraction = "9/16"

            Case 10
                fraction = "5/8"

            Case 11
                fraction = "11/16"

            Case 12
                fraction = "3/4"

            Case 13
                fraction = "13/16"

            Case 14
                fraction = "7/8"

            Case 15
                fraction = "15/16"

        End Select

        result = feet & "'" & "-" & inches & " " & fraction & """"


        Return result


    End Function
Again Thanks
 

Lukan

New Member
Joined
Jun 5, 2004
Messages
20
Thanks for the correct answer, It worked perfect
sorry I took so long to answer
I'm very grateful


Code:
 Public Shared Function Sum_ToNearest_16th(Values As Object) As Object
        '----------------------------------------------------------------------
        '** Demonstrates how to use an array(or range of multiple cells) as
        '   a function argument.
        '   Can be called with formulas such as =GetArray(A1:B5), GetArray(A1),
        '   or GetArray({1,2,3;4,5,6}).
        '----------------------------------------------------------------------
        Dim Sum As Double

        If IsArray(Values) Then

            For Each ObjectValue As Object In Values
                Dim DoubleValue As Double = 0

                If TypeOf ObjectValue Is String Then
                   ' Dim rst As string=""

                  DoubleValue = CDbl(CInches(ObjectValue))
                   ' ObjectValue=rst
                ElseIf TypeOf ObjectValue Is Double
                    DoubleValue = ObjectValue
                Else
                    Double.TryParse(ObjectValue.ToString(), DoubleValue)
                End If

                Sum += DoubleValue

            Next ObjectValue

        Else
            Double.TryParse(Values.ToString(), Sum)
        End If

        Return to_Nearest_16th(Sum)
    End Function
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,504
Messages
5,602,044
Members
414,497
Latest member
guitarmanz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top