Is there "eval" in Excvel VBA? 1

[EdwardMartinIII]

More specifically, how the HECK do I do this sort of thing?

Here's the dum-dum version:

    WebAppDataMatrix(1, 1) = Worksheets("WebAppData").Range("b2").Value
    WebAppDataMatrix(1, 2) = Worksheets("WebAppData").Range("c2").Value
    WebAppDataMatrix(1, 3) = Worksheets("WebAppData").Range("d2").Value
    WebAppDataMatrix(1, 4) = Worksheets("WebAppData").Range("e2").Value
    WebAppDataMatrix(1, 5) = Worksheets("WebAppData").Range("f2").Value
    WebAppDataMatrix(1, 6) = Worksheets("WebAppData").Range("g2").Value
    WebAppDataMatrix(1, 7) = Worksheets("WebAppData").Range("h2").Value

Now, this is just collecting data for one app. I need to populate an array from the spreadsheet where there are FIFTY apps.

So, normally, I'd use an eval statement like this (pseudocode, and I broke up the EvalString assignment for clarity):

dim EvalString as string
dim Counter as integer
For Counter = 1 to 50
  EvalString=""
  EvalString = EvalString + "WebAppDataMatrix("
  EvalString = EvalString + Counter
  EvalString = EvalString + ", 6) = Worksheets("
  EvalString = EvalString + chr$(34)
  EvalString = EvalString + "WebAppData"
  EvalString = EvalString + chr$(34)
  EvalString = EvalString + ").Range("
  EvalString = EvalString + chr$(34)
  EvalString = EvalString + "g"
  EvalString = EvalString + Counter + 1
  EvalString = EvalString + chr$(34)
  EvalString = EvalString + ").Value"
  Eval(EvalString)
Next Counter

But that apparently doesn't work.

Excel has no Eval command?

If it does not (er... huh?!), then what is a good way to do this kind of task?

I have to populate the array from the spreadsheet, and then eventually, push the contents of the array back to the spreadsheet, over-writing the contents of the spreadsheet.

Thanks!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

I expect I'll have to convert the columnar value into a letter, though, wouldn't I?

ABSOLUTELY NOT!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EdwardMartinIII]

You thought wrong."

Do you understand now?


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

ABSOLUTELY NOT!"

Ohhhh, okay, lemme try that...


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

Awesome, Skip!

This is what I used:

  ' Populate the matrix from the spreadsheet
    Dim DataRow As Integer
    Dim DataCol As Integer
    For DataRow = 1 To 50
      For DataCol = 1 To 7
        WebAppDataMatrix(DataRow, DataCol) = Worksheets("WebAppData").Cells(DataRow + 1, DataCol + 1).Value
      Next DataCol
    Next DataRow

I dropped in a bunch of message boxes to test and it worked like a charm!

I'm hoping it'll work in reverse as well, so I can push the values back out to the spreadsheet.

Thanks! Gracias! Dankon! A grateful star for thee!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

Thanks! Glad to help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]