How to use Eval in Outlook VBA? 3

[may1hem]

I'm trying to use the Eval function to join some strings, but I keep getting an "Object required" error.

I know that Eval isn't an Outlook function, and so went to the Tools-->References menu, and selected "Microsoft Script Control.

I simplified the code but still receive the same error. Here's the code.

-----------------------
Dim sTest As String
sTest = sc.Eval("5+7")
MsgBox sTest
-----------------------

Any ideas on why this error occurs?

Thanks,
May

 

[PHV]

What is sc ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[may1hem]

Ok so this works:

--------------------
Set sc = CreateObject("MSScriptControl.ScriptControl")
sc.Language = "vbscript"
Dim sTest As String
sTest = sc.Eval("5+7")
MsgBox sTest
--------------------

but I still receive an "Object required" error if I change the 4th line to evaluate the value of a checkbox

--------------------
sTest = sc.Eval("chkProduct.value")
--------------------

Any ideas why?

Thanks,
May

 

[strongm]

Yes, your script control does not know what chkProduct is ...

Simplest solution: remove the quotes

 

[may1hem]

Thanks Strongm,

chkProduct is a checkbox on my userform. Removing the quote does give the result "True" which is good.

What I really need to do is use an array of checkbox names and a "For Each" loop to test which checkboxes are ticked. Like this:

---------------------------
Set sc = CreateObject("MSScriptControl.ScriptControl")
sc.Language = "vbscript"
Dim sTest As String
productArray = Array("apples", "oranges", "pears", "tomatoes", "potatos", "cucumbers")
For Each product In productArray
sTest = sc.Eval("chk" & product & ".value")
MsgBox sTest
Next product
---------------------------

If I can't use Eval then is there another way to form checkbox names from a list of product names and test the value?

Thanks,
May

 

[strongm]

sTest = sc.Eval(Controls("chk" & product & ".value"))

 

[CBasicAsslember]

You don't need "Eval". This will loop through all the controls and return the name and value for each checkbox.

Sub aTest()
Dim oControl
Dim x
Dim bTest As Boolean
Dim sTest As String

Set oControl = Me.Controls

For Each x In oControl
    If TypeName(x) = "CheckBox" Then
        sTest = x.Name
        bTest = x.Value
    End If
Next
End Sub

 

[strongm]

Step 2 is to dump Eval completely, as you don't need it for what you are trying to do.

Dim sTest As String
productArray = Array("apples", "oranges", "pears", "tomatoes", "potatos", "cucumbers")
For Each product In productArray
sTest = Controls("chk" & product & ".value")
MsgBox sTest
Next product

 

[strongm]

Ah - CBasicAsslember beat me to it

 

[PHV]

And I'd replace this:
sTest = Controls("chk" & product & ".value")
with this:
sTest = Controls("chk" & product).Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[strongm]

Yes, so would I Working too fast without any sort of IDE ...

 

[may1hem]

Thank you VERY MUCH for your guidance, Strongm & CBasicAsslember & PHV.

Now just one thing left... I need to create a variable for each of these products and set an integer value to it.

---------------------------
Set sc = CreateObject("MSScriptControl.ScriptControl")
sc.Language = "vbscript"
productArray = Array("apples", "oranges", "pears", "tomatoes", "potatos", "cucumbers")
For Each product In productArray
Dim sc.Eval("int" & product) As Integer
If Me.Controls("chk" & product).Value = True Then
sc.Eval("int" & product) = 1
End If
Next product
---------------------------

Do I need to use Eval? Is there a better way of generating and setting these variables and their values?

Thanks,
May

 

[strongm]

>I need to create a variable for each of these products and set an integer value to it

Given you can always query the form at any time for the value, why do you need the integer variables that simply have the same value as the checkboxes?

e.g, say we're looking for Oranges ...

intResult=Me.Controls("chk" & "Oranges").Value

 

[may1hem]

I was simplifying my problem to make it easier to solve. There are 4 checkboxes for each product, which represent the next 4 weeks. So I can choose which products are to be delivered on each of the next 4 weeks.

And further on in the code I need to test how many weeks each product is scheduled for delivery. E.g. apples are to be delivered on weeks 1, 2, and 4.

Continuously testing the checkboxes on the form will require too much code, so testing them all once and storing them in variables makes it easier to manage for me.

These variables need to be defined by name so that it's easier for me to read & track.

So is there a way to define variables in a loop as I asked before?

Thanks,
May

 

[strongm]

Why would you need to continuously test the checkboxes? I'm clearly missing something.

 

[may1hem]

I need to count the number of weeks each product is to be delivered.

I need to count this once and store the number in a variable. I refer to this number a lot in my code so using this variable is important; if I don't use this variable then I would need to use count the selected delivery weeks throughout my code.

So how can I use a FOR EACH loop to define a new variable for each of the products in the array?

Thanks,
May

 

[strongm]

>So how can I use a FOR EACH loop to define a new variable for each of the products in the array?

Theoretically you can't. However, since you are insistent that this is what you want to do I'll illustrate a potential solution - but with the caveat that I really don't think that you are taking the best approach here. OK, here's how we do it

1) We will use the Script Control - but don't use CreateObject. Add the Script Control to your program as a reference
2) Add a new module to your project and paste in the following line

[blue]Public sc As New ScriptControl[/blue]

3) Add a userform, and add two command buttons to it. Then paste in the following:

[blue]Option Explicit

Private Sub CommandButton1_Click()
    Dim product As String
    Dim examplevalue As Integer
    
    sc.Language = "vbscript"
    
    product = "oranges"
    examplevalue = 5
    
    ' Here is where we set the constructed variable 
    sc.ExecuteStatement "int" & product & "= " & examplevalue
    
    ' and here is where we read it
    MsgBox sc.Eval("int" & product)
    
End Sub

Private Sub CommandButton2_Click()
    ' Just to show this works in another routine
    MsgBox sc.Eval("intoranges")
End Sub[/blue]

That should be enough to be getting along with

 

[may1hem]

Thanks again for your advice Strongm.

Why don't you think this is the best approach?

May

 

[strongm]

Because you are effectively asking how to write self-modifying code, which is never a really good idea. The solution I've given you simply pretends to do this by running a seperate script interpreter (the variables live in that interpreter not in your code), and the same effect could be better achieved through

a) using a collection or dictionary
b) doing the count whenever you need to (this really isn't as onerous as you seem to be making out)

Here's an illustration for both of the above:

[blue]Option Explicit

Public ProductWeekCount As Collection

Private Sub CommandButton1_Click()
    MsgBox GetWeekCount("oranges") ' do the count right now ...
End Sub

Private Sub CommandButton2_Click()
    CountAllProducts ' count all products and store in a public collection
    MsgBox ProductWeekCount("oranges") ' query collection for a value
End Sub


' Count requested delivery weeks for specific product
Public Function GetWeekCount(strProduct As String) As Long
    Dim lp As Long
    
    On Error Resume Next 'Ignore missing weeks or wrong product names
    For lp = 1 To 4
        GetWeekCount = GetWeekCount + Abs(Controls("chk" & strProduct & lp).Value)  'assuming checkboxes are named chk<productname><weeknumber1-4>
    Next
    On Error GoTo 0
End Function


' Populate our global collection with product week counts
' We can later interrogate the Collection by product name
Public Function CountAllProducts()
    Dim Product As Variant
    Set ProductWeekCount = New Collection
    For Each Product In Array("apples", "oranges", "pears", "tomatoes", "potatos", "cucumbers")
        ProductWeekCount.Add GetWeekCount(CStr(Product)), Product 'note how we use the GetWeekCount function
    Next Product
End Function
[/blue]