代码之家 › 专栏 › 技术社区 › Alex Gordon

从C到VBA转换(是的,有点愚蠢)

vba excel c#

Alex Gordon · 技术社区 · 14 年前

 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 );
  }

这是我的密码。我需要把它转换成vba。

我的主要问题是:

  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 };

我们如何在VBA中执行此操作?

2 回复 | 直到 14 年前

DJ Bouche 14 年前

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

不是最有效的……但是试图遵循原始代码的流程。它将运行缓慢,因为它就像野蛮的循环(大约需要4分钟)。也许通过ADO/DAO使用临时的数据库/访问表可以帮助您使用SQL。

Stu 14 年前

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