0 
 double [] numbers = new numbers[200];
  numbers[0] = 123;
  numbers[1] = 456; 

  //
  // and so on.
  //

  var n0 = numbers;
  var n1 = numbers.Skip(1);
  var n2 = numbers.Skip(2);
  var n3 = numbers.Skip(3);

  var x = from a in n0
          from b in n1
          from c in n2
          from d in n3
          where a + b + c + d == 2341.42
          select new { a1 = a, b1 = b, c1 = c, d1 = d };

  foreach (var aa in x)
  {
    Console.WriteLine("{0}, {1}, {2}, {3}", aa.a1, aa.b1, aa.c1, aa.d1 );
  }

there's my c# code. i need to transate it into vba.

my main problem is this:

  var x = from a in n0
          from b in n1
          from c in n2
          from d in n3
          where a + b + c + d == 2341.42
          select new { a1 = a, b1 = b, c1 = c, d1 = d };

how would we do this in vba?

Improve this question
2
  • 1
    I don't think you can use Linq to Objects in VBA. Commented Nov 12, 2010 at 0:16
  • 2
    I'm guessing the answer will involve loops. A lot of loops. Commented Nov 12, 2010 at 0:18

2 Answers 2

Reset to default
3 
Type NumberSet
    A As Double
    B As Double
    C As Double
    D As Double
End Type

Public Sub TheSub()
    Dim numbers(0 To 199) As Double

    numbers(0) = 123
    numbers(1) = 456

    '
    ' and so on.
    '

    'iterators
    Dim ai As Integer
    Dim bi As Integer
    Dim ci As Integer
    Dim di As Integer

    'data set    
    Dim x() As NumberSet

    'temp record holder
    Dim ns As NumberSet

    Dim count As Integer

    count = 0

    'simulate the select query
    For ai = LBound(numbers) To UBound(numbers)
        For bi = LBound(numbers) + 1 To UBound(numbers)
            For ci = LBound(numbers) + 2 To UBound(numbers)
                For di = LBound(numbers) + 3 To UBound(numbers)
                    'fill the record
                    With ns
                        .A = numbers(ai)
                        .B = numbers(bi)
                        .C = numbers(ci)
                        .D = numbers(di)
                        'test record
                        If .A + .B + .C + .D = 2341.42 Then
                            'append to the data set
                            ReDim Preserve x(0 To count)
                            x(count) = ns
                            count = count + 1
                        End If
                    End With
                Next di
            Next ci
        Next bi
    Next ai

    'iterate through data set and print results
    For i = LBound(x) To UBound(x)
        ns = x(i)
        With ns
            Debug.Print .A & ", " & .B & ", " & .C & ", " & .D
        End With
    Next i
End Sub

not the most efficient.. but tried to follow the flow of the original code.. it will run slow as it's like bruteforce looping (takes about four minutes here).. perhaps using a temporary database/access table via ADO/DAO where you could use SQL would help.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

One of the problems I always had with VBA, I felt like I was trying to jump rope with one hand tied behind my back. Clever...I expect that performance would increase significantly with some sort of temporary data storage as you suggest.
Although I'm not sure if this is specifically an Office project, but I'm puzzled as to why MS didn't try to phase out VBA (or just add an extra language) with some kind of light/dynamic .NET language with "modern language" features.. we're already at Office 2010 and still using VBA. While I understand that it is there for legacy purpose (and they just re-implemented in the 2011 Mac OS X version), and it is heavily used by existing Excel/Access solutions.. they didn't treat VB6 (VB Classic) quite as nicely, did they? After all, the object model is already there and usable in .NET!
"They didn't treat VB6 (VB Classic) quite as nicely, did they?" <-- Just the opposite: VB 6.0 migrated to VB 7.0, which is .NET, as you suggested should be done. Truth is that VBA and .NET share the same (COM) object model. And one can make VB.NET or C# add-ins without much trouble either, so there is no limitation with .NET or with VBA, as I see it.
As for the comments about using a temporary data source, I don't see it, especially something increadibly slow like using a database. One could perform a cutoff evaluation in the loop if, say, A, or A + B, or A + B + C, is already too large. This could potentially cut-off a lot of looping, but only if the values held in the original set of numbers cannot be negative -- and this was not suggested in the question. So there is no choice but to do brute force here.
true.. I just would have liked to see a "migration" integrated into Office itself. I've seen something like a query over a local Access database (especially with a small set of data like 200 records) have good performance, but I might be overlooking the fact that we're testing the A+B+C+D which would imply brute force evaluation as you say.
2 
Dim N(200)

For I0 = 0 To 199
  For I1 = 1 To 199
    For I2 = 2 To 199
      For I3 = 3 To 199
        If N(I0)+N(I1)+N(I2)+N(I3) = 2341.42 Then 
          Debug.Print N(I0) & "," & N(I1) & "," & N(I2) & "," & N(I3)
        End If
      Next
    Next
  Next
Next
Improve this answer

1 Comment

Nitpick: technically your loops should go 0 to 199, 1 to 199, etc. to recreate the 'Skip' part...and your if statement should just sum up n0(i0) + n0(i1) + n0(i2) + n0(i3). If you're looping then you would just need the original array.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.