Topic: Exploring LEAP in Excel/Crystal BallSubscribe | Previous | Next
Chibulu Luo 11/18/2017
Hello,
I am exploring how to use LEAP with Crystal Ball to do some monte carlo analysis on various parameters. I would like to run several iterations of the model.However, I'm having trouble understanding the best approach to using the API as Excel VBA and VBScript are quite different tools. My question is: how can I best automate tasks and feed new model inputs to LEAP, can I do this directly with Excel VBA/Crystal Ball, or do I need to use the Script Editor in LEAP to pass through inputs? I ask because I'm trying to figure out the best tutorial material i.e. do I need to learn Excel VBA or just VBScript? I'm quite new to programming, but keen to get my head around this!
Thanks a lot!
Taylor Binnington 11/29/2017
Hi Chibulu -
Sorry for the delay here. The LEAP API is compatible with any Windows scripting language - this includes VBA, VBScript, and others too. Excel's macro editor interprets VBA, so you may run your VBA from within Excel if you wish. The LEAP "Script Editor" works with VBScript. The Script Editor is intended to be a convenient place to run simple scripts or perform quick operations, but if you have something else in mind I would recommend that you use another development environment (Excel may be adequate, or you might choose something more enterprise-level like Visual Studio).
If you'll be using Crystal Ball, then running all of your scripts in Excel is likely the most convenient way to go since Crystal Ball interacts directly with Excel. When accessing the LEAP Api methods from outside of LEAP's Script Editor, you'll need to instantiate a LEAP object within your code with a statement like:
leap_object = LEAP.LEAPApplication
If you have questions about the API as you work, please be sure to post a minimal working example here in the forum alongside your question so that we or others can try to reproduce exactly the issue you're experiencing and offer the best help. Thanks!
Taylor
Chibulu Luo 11/30/2017
Thanks Taylor! I managed to figure out a bit of the code. I shared a more detailed response on what I'm planning to do in a previous thread/response: https://www.energycommunity.org/default.asp?action=9&tid=3372&fid=22
Basically, my VBA code is as follows:
Sub ChangingModelAssumptions()
Set L = CreateObject("LEAP.LEAPApplication")
L.ActiveArea = "Dar es Salaam and Lusaka"
L.ActiveRegion = "Lusaka"
L.ActiveView = "Analysis"
L.ActiveScenario = "Current Accounts"
'Changing electrification rate
'L.Branch("Demand\Urban Households\Electrified Households").Variable("Activity Level").Expression = Sheets("InputDataDar").Range("B20").Value
'Changing travel distance
L.Branch("Key\Average Trip Distance").Variable("Activity Level").Expression = Sheets("InputDataDar").Range("B10").Value
End Sub
My question is: how do I print results from say 1000 simulations to excel, using randomly generated inputs to cell B20 and B10 in my worksheet (I have a list of about 6 variables that I'd like to randomly change)? I would also like to analyze results directly within Crystal Ball - therefore having the LEAP result somehow "captured" by Crystal Ball for the analysis to be conducted. I know Charles Heaps has mentioned previously that this is possible using a few lines of VBA code. Would you be able to share some example code to do this?
Thanks a lot!