Question:
When I attempt to run the model with AAPL and several other stocks, I get a type mismatch error from Excel, and then in attempting to debug the model code, this is what is displayed (see below.) I realize that it is not recognizing the indicator variables, however these were already assigned near the start of the file.
Is this a flaw or a feature that is due to my having Excel 2013 installed on my computer? What advice or suggestions would you have to correct this issue and have the model work?
Answer:
This issue is caused by having no AAPL price data yet in your DATAv3.xlsm AAPL worksheet. When you click the Run Model button it goes to fetch the data from DATA workbook, copies the cells where there is supposed to be AAPL data (there is none), goes back to the Model Calculations worksheet and pastes nothing there.
With no “fuel” for the calculations you’re getting #DIV/0! errors in the Calculations sheet and the VBA is throwing variable mismatch errors because all it has to work with is “#DIV/0!”.
A little trick: click on the bar just to the left of the VBA code in the VBA Editor. This creates a code stop point. Create several of them, then run the code with the “>” green arrow in the VBA Editor. This will allow you to walk through the code and see where it’s breaking, then mouse over and see the variable values at any given time. When you have the errors worked out you can unclick these stop points and save your model. See below…
Excel Stock Trading Model – VBA Code Stop Points
