Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Can I change code on the fly?

    I have some code like this:

    Code:
    If mycell = a_value then 
         do some work
    end if
    What I'd like to do is to be able to modify the operator (equals) depending on some parameters I read in.

    Is that possible in VB or do I have to have lots of IF statements depending on what I read in?

    cheers

    Alan

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    The answer is absolutely. I will tune in this evening after work if no one else offers a solution.

    Maud

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can use many IFs or the Case structure, depending on what you want to do exactly. Could you be more specific?

    Steve
    PS here is some example code of the 2: http://support.microsoft.com/kb/141691
    Last edited by sdckapr; 2013-08-02 at 06:55.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    More specific. OK.

    I have three columns of parameters as shown:

    Code:
    Heading name          Success Criteria               Operator
    Billing                      Yes                               Equals
    Country                   North America                 Not equals
    So, I read a line off a spreadsheet and do the tests:

    If The value of the column with the heading of "Billing" is "Equal" to "Yes, and
    the value of the column with the heading of "Country" is "Not equal" to "North America"

    then do some work.

    However, I may want to change the parameter file so it looks like this:

    Code:
    Heading name          Success Criteria               Operator
    Billing                      No                                Not equals
    Country                   North America                 Not equals
    In this case, I am testing to see if the value of the Billing cell is not equal to "No" then I do some work.

    I can do it with a load of If's and Case's statements, but it's more elegant if I can just change the operator (= or <>) to make it as flexible as possible.

    Does all that make sense?

    Alan

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    I think you are looking for something like this:
    Code:
    If mycell = a_value then
      If zOperator = "Equals" then
         do this work when result matches
      else
         do this work when mycell doesn't match
      end if
    end if
    zeddy

  6. #6
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Yes, that's the IF style. I was wondering if I could say something like

    Code:
    If <my_cell_value>   <my_operator>  <My expected result>  Do something
    else
    Do something else
    end if
    basically modifying code at run time.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Alan

    Have you looked at whether you could use the vba IIF function?
    The Immediate If Function:
    The Immediate If function has the syntax:
    IIf(Expression, TruePart, FalsePart)

    zeddy

  8. #8
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Very interesting. So, I could build my strings of different tests and then just use the right one as appropriate. My query now is that the example here (from the help):

    Code:
     CheckIt = IIf(TestMe > 1000, "Large", "Small")
    Has the expression as separate items. Can I make it as a string such as:

    Code:
     
    myexpression = "testme > 1000"
     CheckIt = IIf(myexpression, "Large", "Small")
    Actually, I wonder if that will do what I want. Because what I want is if the expression is true (Billable = Yes), then I want to run a whole lot of code - not just set an answer.

    But its worth investigating. Thanks.

    Alan

    Alan

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    Have another beer while you post your reply to the right person!
    Just kidding

    zeddy

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Thanks for the catch!

    Beer isn't the problem it's lack of caffeine. I got started on the Diet Coke a little late this morning!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    You have the best picture.
    And as I look closer, it seems you have not one, but TWO beers in your hands.
    (I previously assumed it was just the one!)
    Definitely not a Diet Coke bottle!

    zeddy

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Actually, neither one is a beer but rather 2 parts to an iron puzzle! I sent that picture (large version) to my wise guy brother-in-law on Christmas morning after I cracked the puzzle he sent me after 38 seconds! That was the best present for me.

    P.S. I only drink beer in Germany as a rule.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts

    Writing VB code on the fly

    TITLE: Can I change code on the fly?
    What I'd like to do is to be able to modify the operator (equals) depending on some parameters I read in. Is that possible in VB or do I have to have lots of IF statements depending on what I read in?
    I can do it with a load of If's and Case's statements, but it's more elegant if I can just change the operator (= or <>) to make it as flexible as possible.
    Alan,
    Since no one presented a solution to your direct question, but rather presented workarounds with conditional statements, I thought I would chime in.

    Using your example, by selecting the operators in F2 and F3 in the file I attached, you can write code on the fly. Into the Sheet1 module a macro called NewCode() will be revised and the operators you had selected will be inserted into the code.

    1. Select operators in F2 and F3 (set with data validation- see image 1)
    2. Click the button to build/change the NewCode macro in the Sheet1 module (see image 2)
    3. Then run the NewCode() routine by clicking the Run button to see the effect of different operators (see image 3)

    This is a very simple example but it can be as complex as you like. It shows that you can create or alter code using VB then run the code to achieve a different effect. View the VB Editor with the Sheet1 module open along side the Excel spreadsheet and toggle the operators. Click the Build Code button Watch the code being modified in front of your eyes.

    HTH,
    Maud

    Select the operators and click the build code button
    CodeOnFly1.png

    Sheet1 module NewCode() will be revised by adding the operators you selected
    CodeOnFly4.png

    Click the Run button to run the NewCode() with the selected operators
    CodeOnFly3.png

    Code:
    Public Sub BuildMacro1()
    'DECLARE VARIABLES
    Dim code As String
    '------------------------------------------
    'SET VARIABLES
    code = "If [c2] " & Cells(2, 6).Value & Chr(34) & "Yes" & Chr(34) & " And [C3] " & Cells(3, 6).Value & Chr(34) & "North America" & Chr(34) & " Then"
    '-----------------------------------------
    'REPLACE UPDATED OPERATORS LINE 2 NEWCODE ROUTINE IN SHEET1 MODULE
    ThisWorkbook.VBProject.VBComponents("Sheet1").Activate
    With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
        .DeleteLines 2
        .InsertLines 2, code
    End With
    '-------------------------------
    'CLEAR VARIABLES
    code = vbNullString
    
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-08-03 at 11:07.

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Well, a solution might be to use a UDF, ie.
    Code:
    If myFunction(myCellValue, myOperator, myExpectedResult) then
    do this if true
    Else
    do that if false
    End If
    zeddy

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Alan,

    The codes above handle one operator and value. With 2 operators there could be 4 different combinations. With 3 operators, 8 or (2^n). How many conditional (if/case) statements do you want to write in your code? How difficult would it be to debug it? Add in more than 2 operators and/or varying values, and it's a whole new ball game!

    Having only one conditional statement but changing the operators/values on the fly in that one statement makes so much more sense from a coding/debugging/operational point of view especially as the number of operators and values increase.

    ex. If [c2] <Operator> <Value> And [C3] <Operator> <Value> And [C4] <Operator> <Value> And [C5] <Operator> <Value> Then


    Maud

    Note: This code could also be transformed into a UDF
    Last edited by Maudibe; 2013-08-04 at 14:35.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •