Using A List Containing Variables In VBA

[adamanthaea]

I am currently working on translating a program out of Mathematica into VBA. The program uses a combination of Excel and VBA to pass data into Mathematica to do calculations and then back out to Excel. Due to a VBA limitation in the amount of variables that can be passed, the previous programmer used lists that were then decoded by Mathematica. For ease of translation as well as consistency, I am hoping to do the exact same thing using VBA functions and subroutines. However, I am not sure how to decode a list like that for use. I believe it is being passed as a string, but I am not sure. Any help would be greatly appreciated.

 

[SkipVought]

adamanthaea,

The correct answer will depend on the format of the passed list.

So is it a string or is it an array???

Might be a good idea to give more detail and post some code.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[adamanthaea]

Well, it's kind of hard to pry all of it apart, but I'll try to give an example. I am pretty sure that it's coming as a string, but part of the problem is that I'm not that familiar with the Mathematica language.

From the VB:

For compn = 1 To ncomp
    Sheet22.Activate 'Get column variables to be passed to Mathematica,
        Rem directly when possible, otherwise via list colvar.
        rown5 = 2 * compn
        lngth = Cells(rown5, 2)
        Cells(30, 2) = lngth '=colvar[[1]]
        d0 = Cells(rown5, 3) * 0.001 'convert from mm to m
        Cells(30, 3) = d0  '=colvar[[2]]
        fd = Cells(rown5, 4) * 0.000001 'convert from um to m
        Cells(30, 4) = fd '=covar[[3]]
        po = Cells(rown5, 6) * 6894.757 'convert from psia to Pa.
        Cells(30, 6) = po '=colvar[[4]]
        td = Cells(rown5, 7)
        kdt = Cells(rown5, 8) + 273.15
        xvar = Application.Run("Mathematica", "pratio", lngth, d0, fd, po, kdt, td)
        If xvar = "$LinkError" Then
            resetlink
            xvar = Application.Run("Mathematica", "pratio", lngth, d0, fd, po, kdt, td)
        End If
        pr = CSng(xvar)
        Cells(30, 5) = pr '=colvar[[5]]
        pi = po * pr / 6894.757 - 14.69595 'Store inlet pressure psig while we are at it
        Cells(rown5, 5) = pi
        Cells(rown5 + 1, 5) = pi
        Rem Pass variables length, d0, fd, pr, po into Mathematica using the list covar
        Application.Run "MathDefine", "colvar", Range(Cells(30, 2), Cells(30, 6))

Then the Mathematica looks similar to

caspdtest[tr1_, tr2_, kcol1_, kcol2_, dhe_, dse_] :=
  If[Check[thermo[colvar[[1]], colvar[[2]], colvar[[3]],
   colvar[[4]], colvar[[5]], tr1, tr2, kc[Partition[kcol1, 
          2]], kc[Partition[kcol2, 2]], dhe, dse], "err"] === "err"
    , "err", "ok"]

There's a lot of code in this program, but I think that this is fairly representative of what's going on in most of these cases.

 

[SkipVought]

Here's an example of a function (Chop) that returns an array (b) of undetermined elements.

Sub test()
   Dim b As Variant, i As Integer
   b = Chop("abcdefghijk", 3)
   For i = 1 To UBound(b, 1)
      MsgBox b(i)
   Next
End Sub
Function Chop(MyString As String, iNum As Integer) As Variant
  Dim i As Integer, a As Variant, n As Integer
  n = Int((Len(MyString) - 1) / iNum) + 1
  ReDim a(1 To n)
  For i = 1 To n
    a(i) = Mid(MyString, (i - 1) * iNum + 1, iNum)
  Next
  n = Len(MyString) - n * iNum
  If n > 0 Then a(UBound(a, 1)) = Right(MyString, Len(MyString) - n)
  Chop = a
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884