Use VBA to update PPT from Excel

This is a journal recording my process of learning VBA to update PPT from Excel sheet. MS Office version: 2016.

The first two things: 

1. open VBA by alt+F11 in an opened office program. 

2.[ If control Excel in PowerPoint]  In "Tool"->"References"  choose MS Excel 16.0 Object library (otherwise will have error "object not found") 

   [ If control PowerPoint in Excel]  In "Tool"->"References"  choose MS PowerPoint 16.0 Object library

3. My first design was pull data from Excel to PowerPoint. One difficulty is how to find shape names. Then I found a video "https://www.youtube.com/watch?v=WcxHgo8k6CA" which introduces another structure: use one excel sheet as a buffer area (Sheet 1) between PowerPoint and Excel. First we pull data one by one from PowerPoint shapes to sheet 1. Thus we have a list of shapes we need to update and we know their slides, names and current value. Second, we update data in excel and push those data pack to their PowerPoint positions. 

4.  The code in the video

<pre class="lang-vb prettyprint prettyprinted" style="margin-top: 0px; margin-bottom: 1em; padding: 5px; border: 0px; font-size: 13px; width: auto; max-height: 600px; overflow: auto; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; color: rgb(57, 51, 24); word-wrap: normal; background-color: rgb(239, 240, 241);"><code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Dim</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> ppapp </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">As</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> PowerPoint</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">Application
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Dim</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> pppres </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">As</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> PowerPoint</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">Presentation

</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Sub</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> getshapedata</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">()</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">On</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Error</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">GoTo</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> line1
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Set</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> ppapp </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> GetObject</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">(,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; color: rgb(125, 39, 39);">"Powerpoint.application"</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Set</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> pppres </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> ppapp</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">ActivePresentation

</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Dim</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> shapeslide
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Dim</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> shapename
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Dim</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> shapetext
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">Dim</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> nextrow

shapeslide </span><span class="p