Crystal Ball is the widely used decision making software from Oracle Corporation. You can use it for forecasting, simulation, optimisation and predictive modelling tasks that help in maintaining competitive advantage. Visual Basic for Applications (VBA) is the programming language used for all the Microsoft Office products. Since Crystal Ball can be accessed from Microsoft Excel as an Add-In, you can use Visual Basic Macros (small functions and subroutines) for running Crystal Ball. Use VBA to perform tasks like multiple simulations at a time, to integrate with other tools and generate customised reports.
- Skill level:
Click Windows "Start" and choose "All Programs." Locate and choose "Crystal Ball" from the available list of programs. The "Crystal Ball" toolbar will appear in Excel Ribbon interface.
Create your model in Excel. Click "Developer" in the main menu and choose "Visual Basic" to open Microsoft Visual Basic Editor (VB). Alternatively, you can also open VB by pressing "Alt and F11." You will be able to see the code behind the model created in Excel.
Click "Insert" from the main menu and choose "Insert Module" to open "Module1" screen. You can write your VBA code in this screen.
Click "Tools" from main menu and select "References" to open the "References -- VBA Project" screen.
Locate and check-box the option "cb.xla" or "CBDevKit.xla" depending on the version you are using to enable Crystal Ball functions in VB. If you do not find the file in the list of References, locate it using the "Browse..." tab and choosing the appropriate file.
Write your code in the "Module" window. Call Crystal Ball functions by prefixing "CB" to them. For example, to run a simulation 100 times, you can call function in the following way:
CB. Simulation 100.
Close "Visual Basic Editor" once your code is complete by clicking the "X." Click "Macros" in the "Developer" menu to see the list of macros available.
Click the appropriate macro by highlighting it and select "Run" to execute the macro. You can also assign the macro to a button or any other control like Combo Box.
Click "Developer" in the main menu and choose "Insert" in the "Controls" sub-menu. Select one of the available controls and specify a location in the spreadsheet. "Assign Macro" screen will open. Select a macro from the available list and click "OK" to complete the process.
Tips and warnings
- Crystal Ball must be running and referenced in the VBA program for calling Crystal Ball functions from VBA.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for