Excel VBA: programmatically insert PY( ) call in worksheet cell?

[hrob45]

Hi--In Excel, I need to programmatically insert a call to the interesting new PY( ) function in a worksheet cell, with return as an Excel value (not python object). Tried repeatedly using a few different ways, but all failed--see simple example below that shows how I tried, and the resulting errors).

Note that manually creating a PY( ) call with exactly the same test python code works fine, so clearly I'm doing the "insert from VBA" wrong. Searched but did not find anything online on this. Anyone succeeded in doing this, please?

Would be grateful to hear from anyone who's succeeded in doing this, or who can point me to any Microsoft guidance on this specific problem (I failed to find such).

Thanks,
Rob

Details:
========
Environment:
Windows Prof 64bit, Office 365, Excel 32bit (Insider Beta ver 2311 build 16.0.17005.20000)

Simple steps to recreate problem:
- Create a new .xlsm workbook (single worksheet)
- Create a VBA module, and define subroutine TestPyInsert() (see below)
- Run this subroutine, wait for newly-created PY() call to complete,
then save & close workbook
- Re-open the workbook--Excel pops up 2 ~error messages:
1) "We found a problem with some content in <mywkbk.xlsm>.
Do you want us to try to recover as much as we can? ..."
2) "Repairs to <mywkbk.xlsm>: Excel was able to open the file
by repairing or removing the unreadable content.
(...then lists 2 removed records--calc chain corruption?)

Please Login or Register  to view this content.

[leelnich]

Hi hrob45, welcome to the forum.
I don't have Office365, so I'm unable to experiment, but if Python code entries must be confirmed with CTRL+ENTER, I suspect VBA's normal Range.Formula construction won't work. I base this conclusion on the way VBA handles Array Formulas, which also require CTRL-sequence confirmation in older versions of Excel. These are assigned programmatically as Range.FormulaArray.

Did you try recording a manual entry of your Py() formula as a macro?

[hrob45]

Wonderfully sensible and simple idea of using the recorder to tease out the correct syntax...that did not occur to me to try--thank you! Tried and found the solution:

It seems that to programmatically insert a =PY( ) call in a cell (see my prior post for my OS, Excel version, etc), what works for me is this VBA code:

Please Login or Register  to view this content.

Many thanks to leelnich for the prompt and valuable suggestion. Mods, assuming no one disagrees, please kindly mark inquiry as solved.
Thanks again,
Rob

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

[hrob45]

Thanks--now done.

[leelnich]

You're welcome, thanks for sharing your solution...
NOTE : As the original poster/owner, only you can mark your thread as SOLVED (Thread Tools above Post # 1). - Lee