Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Andrzejek on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is there "eval" in Excvel VBA? 1

Status
Not open for further replies.
EdwardMartinIII

EdwardMartinIII

Technical User
Sep 17, 2002
1,655
US
More specifically, how the HECK do I do this sort of thing?

Here's the dum-dum version:

Code: 
    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):

Code: 
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!


[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Sort by date Sort by votes
Whoops, sorry about the typo in the title.

Also, changed my example to this:

Code: 
    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

[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
(I'm just using column 6 as an example. I have 50 rows and 7 columns)


[monkey] Edward [monkey]

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


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Sorry -- I missed two things.

First, I'm working in Excel 2007.

Second, in my example,

Code: 
MsgBox (EvalString)

should be

Code: 
eval (EvalString)


[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
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?


[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
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.



[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
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:

Code: 
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
 
Upvote 0 Downvote



Evaluate()


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
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.


[monkey] Edward [monkey]

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



...without doing it using 350 individual programming lines.
Click to expand...
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Evaluate()"

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

This was the test code I used to test it:

Code: 
  ' 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.


[monkey] Edward [monkey]

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



so why not simply...
Code: 
WebAppDataMatrix(1, 1) = Worksheets("WebAppData").Range("b2").Value

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
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.


[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
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!


[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
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.


[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
I should mention that the code line:

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

did produce the proper string from the spreadsheet.


[monkey] Edward [monkey]

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


Would this not do it???
Code: 
    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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
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?


[monkey] Edward [monkey]

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
396
combo
combo
xwb
  • Locked
  • Helpful tip
Performing VBA operations in other languages
Replies
1
Views
285
strongm
strongm
Andrzejek
  • Locked
  • Question
Outlook VBA - Trust access
Replies
3
Views
581
hermanlaksko
hermanlaksko
kapok1
  • Locked
  • Question
how to use vba to forbid "Worksheet Menu Bar" being selected
Replies
6
Views
301
kapok1
kapok1
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
338
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top