Running VBA on the Multiple spreadsheet

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
rich789
Posts: 6
Joined: Fri Feb 10, 2023 12:59 pm

Hi all

I've been running VBA on the Single spreadsheet successfully but am looking to move to the multiple so that I can keep markets in the sheet until they have closed.

How are you running VBA in this scenario, from every sheet or from the workbook/a module?

The problem I have is that I'm calling some Python from the VBA. In the Python on the single sheet I can then do something like:

Code: Select all

import xlwings as xw
def myfunction():
wb = xw.Book.Caller()
wb.sheets['Bet Angel']['A1'] = "foo"
But because the sheet name of 'Bet Angel' is hard coded this won't work on the multiple sheet

I've been looking for a way to dynamically get the name of the sheet that is running the VBA (and just have my VBA on each sheet), with something like:

Code: Select all

Application.Caller.Worksheet.Name 
which I found in a Stack Overflow answer but that doesn't seem to work. I'm hoping there is something I'm missing where I can pass the name of a sheet, that satisfies my criteria to run, to python dynamically.

Could the Workbook_SheetChange method be an option? Any pointers much appreciated.

Thanks, Rich
User avatar
Derek27
Posts: 23478
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

rich789 wrote:
Fri May 05, 2023 7:57 pm
Hi all

I've been running VBA on the Single spreadsheet successfully but am looking to move to the multiple so that I can keep markets in the sheet until they have closed.

How are you running VBA in this scenario, from every sheet or from the workbook/a module?

The problem I have is that I'm calling some Python from the VBA. In the Python on the single sheet I can then do something like:

Code: Select all

import xlwings as xw
def myfunction():
wb = xw.Book.Caller()
wb.sheets['Bet Angel']['A1'] = "foo"
But because the sheet name of 'Bet Angel' is hard coded this won't work on the multiple sheet

I've been looking for a way to dynamically get the name of the sheet that is running the VBA (and just have my VBA on each sheet), with something like:

Code: Select all

Application.Caller.Worksheet.Name 
which I found in a Stack Overflow answer but that doesn't seem to work. I'm hoping there is something I'm missing where I can pass the name of a sheet, that satisfies my criteria to run, to python dynamically.

Could the Workbook_SheetChange method be an option? Any pointers much appreciated.

Thanks, Rich
I have a 15-sheet workbook. I keep all my code in a class module and use an object variable in each sheet to create an instance of it. That way you can have the same skeleton code in each sheet, keep all your code in the class module and each sheet can call it with a separate instance of all the variables.
rich789
Posts: 6
Joined: Fri Feb 10, 2023 12:59 pm

Hi Derek

Thanks for the reply. That makes a lot of sense. I've been putting this in place today and have got it up and running now. Thanks for your help!

Rich
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”