• 375 views | 8 messages Discussion: LEAP
    Topic: VBA expression of Interp functionSubscribe | Previous | Next
  • Chibulu Luo 12/7/2017

    Like

    Hello,

    I am trying to use the Interp function in excel VBA to change expressions to 2100.

    this is my code:



    'Change charcoal consumption on non-electrified households



    L.Branch("Demand\Urban Households\Non Electrified
    Households\Cooking\Charcoal").Variable("Activity
    Level").Expression = Interp(2100, ActiveCell.Offset(0, 1).Value)

    But it does not seem to work.

    Chibulu





  • Taylor Binnington 12/12/2017
      Best Response

    Like

    Hi Chibulu -

    What, specifically, is not working? Can you post a minimal working example?

    My first guess is that you will need to treat the "Interp(2100," and trailing ")" portion of the expression's right hand side as a string in VBA, since Excel will not otherwise know how to interpret it.

    Taylor

  • Chibulu Luo 12/13/2017
      Best Response

    Like

    Thanks Taylor! I figured it out in the end! The way you suggest works!
  • Gregers Larsen 1/9/2018
      Best Response

    Like

    If you got it working, would you post it? I have general issues getting LEAP to function from VBA.

  • Ioannis Sfikas 5/24/2019
      Best Response

    Like

    Dear Sir/Madam,

    I would like to run the following script:

    For i = 1 to 5
    LEAP.Branch("Transformation\Electricity Generation\Processes\Lignite").Variable("Capital Cost").Expression = "Interp(2030;2075*i;"2040;2075*2*i)"
    LEAP.Calculate(FALSE)
    ExportResultsXLS(XLTabTitle)
    Next
    Inside the Interp function i is not 1,2,...,5 but simply i. How can I treat this problem?

    Thank you in advance

  • Charlie Heaps 5/28/2019
      Best Response

    Like

    Hello Ioannis,

    I think there are a few issues with that code. In particular its not clear to me why you would be running a script to set an expression within a loop and repeatedly running LEAP. Could you tell us more about what you are trying to do and what the i variable is intended to represent?

    But to answer your very particular question, the reason the code would fail at this line...

    LEAP.Branch("Transformation\Electricity Generation\Processes\Lignite").Variable("Capital Cost").Expression = "Interp(2030;2075*i;"2040;2075*2*i)"

    ...is because you have written the right-hand-side (RHS) of the equation (the Interp expression) as a simple string. Notice that the whole of the RHS is enclosed in quotes, so VBscript will interpret that literally. It does not know that you are intending to use the i as a variable! You could write the RHS instead like this..

    ="Interp(2030;2075*" & i & ";"2040;2075*2*" & i & ")"

    When you do that, VBScript will construct an expression out of the quoted text along with the interpreted value of the i variable.

    I hope this helps!

    Best,

    Charlie

  • Charlie Heaps 5/28/2019
      Best Response

    Like

    PS - In future, please do not add additional questions to a pre-existing discussion thread. This makes the Q&A harder to find for other users and also means that replies are sent to the people who started the original thread.

    Thanks for your understanding!

    Charlie

  • Ioannis Sfikas 5/28/2019
      Best Response

    Like

    Dear Dr. Heaps,

    I just tried to build an expression in order to investigate how the results are changing when I change for instance the capital cost of a transformation technology.

    This expression seems to work.

    Thank you so much for your help!