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

 

[EdwardMartinIII]

Whoops, sorry about the typo in the title.

Also, changed my example to this:

    Dim EvalString As String
    Dim Counter As Integer
    For Counter = 1 To 5
      EvalString = ""
      EvalString = EvalString + "WebAppDataMatrix("
      EvalString = EvalString + CStr(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 + CStr(Counter + 1)
      EvalString = EvalString + Chr$(34)
      EvalString = EvalString + ").Value"
      MsgBox (EvalString)
    Next Counter

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]

(I'm just using column 6 as an example. I have 50 rows and 7 columns)


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]

Hi,

How can an EQUALITY be evaluated, except as TRUE or FALSE?

I guess I do not understand.

BTW, [] evaluates what is enclosed.

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

 

[EdwardMartinIII]

Sorry -- I missed two things.

First, I'm working in Excel 2007.

Second, in my example,

MsgBox (EvalString)

should be

eval (EvalString)

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]

The Big Picture view is this:

I have a Userform that -- when it opens -- sucks a bunch of Current Data from the spreadsheet and populates an array.

That Userform is a worksheet that allows the User to modify and tweak those values willy-nilly.

If the User hits [Cancel], then the Userform simply closes and no changes are saved to the spreadsheet.

If the User clicks [Accept Changes] then all the values from the array decalred as part of the Userform creation are written to the the corresponding cells in the spreadsheet, and THEN the Userform is closed.

Does that make more sense?


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]

If there's another way to accomplish this, I'm open to ideas.

This just seemed like a natural idea to me. It simply never occurred to me that there might not be an "eval" type statement in Excel.



Edward

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

 

[mintjulep]

I guess we'll have to guess what your question is.

I'll guess you are interested in how to handle multi-dimensional arrays in VBA, since you mentioned array in your OP, but have shown no code that handles arrays.

Something like this:

Dim myArray () as String
Dim R as Integer
Dim C as Integer

For R 1 to 10
    For C = 1 to 10
       Redim Preserve myArray(1 to R, 1 to C)
       myArray (R, C) = "Whatever"
    Next C
Next R

 

[SkipVought]

Evaluate()


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

 

[EdwardMartinIII]

I guess we'll have to guess what your question is."

No, that's not necessary.

The first question was "Is there "eval" in Excel VBA?"
The second question was "Excel has no Eval command?"
The third question was "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.)

So, if you want to guess, guess one of those.

I also thought I clarified pretty well subsequently.

"I'll guess you are interested in how to handle multi-dimensional arrays in VBA, since you mentioned array in your OP, but have shown no code that handles arrays."

Once I have the data in the array, I have no problem making the array do whatever I need. It's getting the data from the spreadsheet to the array and then, after I'm done having my way with the data, transferring it back to the spreadsheet...

...without doing it using 350 individual programming lines.


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]

...without doing it using 350 individual programming lines.

WOW! REALLY?

let's start with a good explanation of the DATA that is read from the sheet, the DATA that the user tweeks and the DATA that is written back.

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

 

[EdwardMartinIII]

Evaluate()"

Yeah, that was what I thought might do it, too, but no luck.

This was the test code I used to test it:

  ' Populate the matrix from the spreadsheet
    Dim EvalString As String
    Dim Counter As Integer
    For Counter = 1 To 5
      EvalString = ""
      EvalString = EvalString + "WebAppDataMatrix("
      EvalString = EvalString + CStr(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 + "b"
      EvalString = EvalString + CStr(Counter + 1)
      EvalString = EvalString + Chr$(34)
      EvalString = EvalString + ").Value"
      MsgBox ("EvalString = " & EvalString)
      
      Evaluate (EvalString)
      
    Next Counter
    
    MsgBox ("The value of cell B2 is: " & Worksheets("WebAppData").Range("b2").Value)
    MsgBox ("WebAppDataMatrix(1,6) = " & WebAppDataMatrix(1, 6))

If evaluate really did what I expected eval to do, then the value of WebAppDataMatrix(1,6) should have been whatever's in B2 of the spreadsheet WebAppData.

Which, sadly, didn't happen.


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]

so why not simply...

WebAppDataMatrix(1, 1) = Worksheets("WebAppData").Range("b2").Value

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

 

[EdwardMartinIII]

WOW! REALLY?

let's start with a good explanation of the DATA that is read from the sheet, the DATA that the user tweeks and the DATA that is written back."

The data are various strings in each cell (such as, oh, programming language, OS, etc.)

For each application (there are fifty), there are seven different values. 7 * 50 = 350.

My dum-dum example in the base note was pulling the strings over for the first application only, as an example of the formatting of the individual calls.

There are fifty applications.


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]

so why not simply..."

Because that would require 350 lines of code for the array, which is 50 x 7.

I could do it, I guess, if I absolutely had to, but that seems... really dum-dum. Excel can't be that dum-dum!


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]

And again, if there's a slick way to transfer a 50x7 block of cell values into a 50x7 array of strings, and then eventually push the array values back into the cells, I'm open to ideas.

I just naturally thought of doing it this way because most of the languages I've used in the past had some sort of eval statement.


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]

I should mention that the code line:

MsgBox ("The value of cell B2 is: " & Worksheets("WebAppData").Range("b2").Value)

did produce the proper string from the spreadsheet.


Edward

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

 

[mintjulep]

I also thought I clarified pretty well subsequently.

You thought wrong.

 

[SkipVought]

Would this not do it???

    Dim lRow As Long, iCol As Integer
    
    For lRow = 2 To 100   'or as many rows of data in your table
        For iCol = 1 To 8  'or as many columns in your data
            WebAppDataMatrix(lRow - 1, iCol) = Worksheets("WebAppData").Cells(lRow, iCol).Value
        Next
    Next

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

 

[EdwardMartinIII]

Would this not do it?"

Lemme give it a try and get back to you.

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


Edward

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