Excel - VBA | Arrays | Loop

Question

I would like to ask you for some help regarding following issue:

I have two variable (1) outManual : 183.2 and (2) outSystem : 192.5. I also have an array with 8 values. All this being work related, i'll use random values.

My code looks like this:

    Sub calcul()

Dim outManual As Integer
Dim outSystem As Integer
Dim vParam As Variant
Dim i As Integer
Dim x As Integer
Dim diff As Integer

outManual = 183.2
outSystem = 192.5

vParam = Array(0, -13.2, -6, 8.9, 12, 25.5, 4, 7.3)

For i = LBound(vParam) To UBound(vParam)
    For x = UBound(vParam) To LBound(vParam) Step -1

....
End Sub

vba

Is there any code that i could use in order to found out which of the vParam's values (or combination of values) should be add or substracted to variable outManual in order to obtain the second one?

I'm not sure i've explained the situation very well, but I hope you understand. :)

Thank you very much,
Valentin

Use Double - not Integer. Integer (better, Long) is for whole numbers. — Tim Williams, Jan 27 '17 at 21:25
BTW, the search term you're looking for is [Subset sum problem](https://en.wikipedia.org/wiki/Subset_sum_problem). [You can get the gist of how to solve it here (non-VBA specific)](http://stackoverflow.com/a/4633515/4088852). — Comintern, Jan 27 '17 at 21:28
@TimWilliams the double data type is very limited –1.79 to –4.94. Hope I'm not wrong. — Vali George, Jan 27 '17 at 21:34
I could not get what exactly you want to do. It is better to give an example. — Ibo, Jan 27 '17 at 22:15
Maybe you missed the exponential on those numbers ? http://www.functionx.com/vbaexcel/keywords/double.htm — Tim Williams, Jan 27 '17 at 22:20
Integer is 16-bit integer, Long is 32-bit integer, Double is 64-bit floating point. See [Data types and limits](http://stackoverflow.com/documentation/vba/3418/data-types-and-limits/11781/double#t=201701272239400393657) on Docs.SO: Goes **from -9,007,199,254,740,992 to 9,007,199,254,740,992**. I wouldn't call that "very limited". — Mathieu Guindon, Jan 27 '17 at 22:40
@Mat'sMug, Tim - My bad! I was talking about Decimal. Sorry for the confusion, — Vali George, Jan 27 '17 at 23:04

CMArg · Answer 1 · 2017-01-27T22:58:10.683

Edit This can check for sum or substract a value, and guide you for other combinations. But combinations are hard-coded.

Sub calcul()

    Dim outManual As Double
    Dim outSystem As Double
    Dim vParam As Variant
    Dim i As Integer
    Dim k As Integer
    Dim l As Integer

    outManual = 183.2
    outSystem = 192.5
    l = 0

    vParam = Array(0, -13.2, -9.2, 8.9, 12, 25.5, 4, 7.3)

    For i = LBound(vParam) To UBound(vParam)
        If (outManual + vParam(i) = outSystem) Then
            l = l + 1
            MsgBox "Sum element # " & i & " : " & vParam(i)
            Exit For
        ElseIf (outManual - vParam(i) = outSystem) Then
            l = l + 1
            MsgBox "Substract element # " & i & " : " & vParam(i)
            Exit For
        End If

        For k = LBound(vParam) To UBound(vParam)
            If (outManual + vParam(i) + vParam(k) = outSystem) Then
                l = l + 1
                MsgBox "Sum element # " & i & " : " & vParam(i) & " and element # " & i & " : " & vParam(k)
                Exit For
            'ElseIf 'here all other combinations (-i+k, -i-i, i-k)
            End If
        Next k
    Next i

    If (l = 0) Then
        MsgBox "No numbers found in array"
    End If
End Sub

i did not think it's such a complex thing at the first sight. Now i do :). I've also visited the link posted by Comintern and have the confirmation. May this topic be considered close. Thank you very much! — Vali George, Jan 27 '17 at 23:09
ok. Anyway, I'm curious. If you get the code, please post it as an answer. I would like peep it... — CMArg, Jan 27 '17 at 23:47

Excel - VBA | Arrays | Loop

1 Answers1