0

I am having a problem moving a shape created by BERT in my VBA code. If I run the VBA macro that calls Bert to create the chart, and then submit in a second call for the code to move the shape, it works. If I try to combine the code it does not work. I think it's possible the shape does not yet exist when I combine calls. I can also make it work if I call the move code from a worksheet event such as "SelectionChange." However, this make the application look awkward.

https://github.com/sdllc/Basic-Excel-R-Toolkit/issues/116

Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
Aaron Moynahan
  • 1
  • 1
  • 1
    Please post relevant code in the question itself to create a [mcve] rather than post links to code. Note the "M" in "Minimal Complete Verifiable Example" -- you don't need to post all code, just enough code to show what the problem is. – John Coleman Apr 19 '18 at 15:14
  • The code is in the attached link. However, it seem like a lot to ask for to ask someone to download the workbook, and VBA code so they can run the model to answer my question. – Aaron Moynahan Apr 19 '18 at 16:37
  • "However, it seem like a lot to ask for to ask someone to download the workbook, and VBA code so they can run the model to answer my question." Yes, it is -- so why are you doing so? Why not make your question self-contained by finding the shortest code needed to reproduce the problem? Please read [mcve]. – John Coleman Apr 19 '18 at 18:51
  • I solved my own problem. The way I wrote the VBA there are no errors. Also, if you run the program that created the chart it works, and if you run the code that moves the chart, that also works. So no error messages, and my code works when you run it one item at a time. So my question was to see if someone else encountered the problem. Also, the code makes no sense unless it is run in Excel, you have BERT installed, and you have the appropriate packages installed. – Aaron Moynahan Apr 20 '18 at 16:18
  • It's not unusual that processes in VBA have timing issues. Even without accessing COM or R. I've found that it helps to put such processes in separate procedures, and call these procedures from a central procedure. Since your two pieces work separately, keep them separate, and call them sequentially from your main sub. – Jon Peltier Apr 22 '18 at 01:30

1 Answers1

0

So I'm not sure why this happens, but it has something to do with the interaction between VBA, COM and R. In any event you can work around it by adding a shape with the name first (if it doesn't already exist).

duncan
  • 446
  • 3
  • 7
  • Thanks. I actually did what you suggested and now the problem no longer exists. In my original code I thought I every time I ran the model I would delete the chart, and then rebuild them However, when I tried to re-build the chart in the same click to created them, they were not in the shape collection when I tried to move it. So now the first time I run the model the chart is misplaced, but if I run the model again it moves to the right place. – Aaron Moynahan Apr 20 '18 at 16:14