Hi all, was hoping someone can solve this for me and assist me in my requirement. I use Excel 2013 and have adopted a workbook with tables and names. I have also created my own tables in the workbook.
The requirement is quite unique .... firstly, the workbook is filled with streaming data and constant calculations and secondly the user needs to still be able to engage with the workbook. So in many respects, latency is a huge issue so the code needs to run in the background without any overwhelming sapping of resources. It really does require some high performance code to make this useable. I wish I could offer more clues but reality is my VBA isn't great at all.
Here goes .... (note the tables, named cells etc, are on different sheets in the workbook)
I have a named cell called EMSXAutoOnOff
This cell is validated to accept only the following values ...
EMSX Engine On = Auto .... this should automatically trigger the macro below
EMSX Engine On = Manual .... this should only trigger the macro below if a button is selected
EMSX Engine Off .... this will switch of the macro off
I have a table called StagingChain.
This table will only have a header row and just one data row. The data row will be dynamically changing.
The header names in the table will always have an exactly named corresponding header name in the table EMSX_Stage_cf765742
I also have a table called EMSX_Stage_cf765742
This table will have a header row and one or more data rows
This table will most likely have more header rows than StagingChain table, and that's all ok and not an issue for us.
There also appears to be a defined range called EMSX_STAGE_INSERTROW
This named range will always be the last row in the table EMSX_Stage_cf765742
What we want to achieve: .... the Rules....
1. Check in table StagingChain under column heading Action Trigger that the top (and only) data cell has the text TRUE ... if this is not TRUE then do nothing, otherwise if this is TRUE then ....
2. Validate that all column headings in table StagingChain have a similar column heading in table EMSX_Stage_cf765742 .... this is a validation for us and its only a one way requirement given that we are ok if table EMSX_Stage_cf765742 has more columns..... if false then pop alert to user about this, otherwise if TRUE then ....
3. Copy all of the data cells in the first row below the header row of table StagingChain (remember there should only be one row of data in this table) and then ....
4. Check each copied cell does not have any error values (perhaps no values beginning with # ... blanks are ok .... the data is constantly changing so I need to make sure we don't pick up any values that are not correct .... note, you may want to put this part in before step 3 as long as that definitely eliminates picking up wrong values) .... if this step = False then opt out of macro, otherwise if TRUE then ...
5. Paste Special the data values under the same corresponding header name in table EMSX_Stage_cf765742 and in the row defined as EMSX_STAGE_INSERTROW which should be the last row of that table.
6. If named cell EMSXAutoOnOff is equal to EMSX Engine On = Auto then continue monitoring for step 1 ... if FALSE then ... If EMSXAutoOnOff is equal to EMSX Engine On = Manual then only monitor when button is pushed for one instance ... if FALSE then macro is off..... I suppose Step 6 becomes Step 1!
My take on it ... I presume the monitoring stage is where we will need some funky code so that the spreadsheet doesn't freeze or sap resources ... perhaps the trigger to TRUE in step 1 needs a lot of attention? ... also, a macro flicking through sheets will be a user distraction so we want toavoid if possible.
thanks heaps in advance.
The requirement is quite unique .... firstly, the workbook is filled with streaming data and constant calculations and secondly the user needs to still be able to engage with the workbook. So in many respects, latency is a huge issue so the code needs to run in the background without any overwhelming sapping of resources. It really does require some high performance code to make this useable. I wish I could offer more clues but reality is my VBA isn't great at all.
Here goes .... (note the tables, named cells etc, are on different sheets in the workbook)
I have a named cell called EMSXAutoOnOff
This cell is validated to accept only the following values ...
EMSX Engine On = Auto .... this should automatically trigger the macro below
EMSX Engine On = Manual .... this should only trigger the macro below if a button is selected
EMSX Engine Off .... this will switch of the macro off
I have a table called StagingChain.
This table will only have a header row and just one data row. The data row will be dynamically changing.
The header names in the table will always have an exactly named corresponding header name in the table EMSX_Stage_cf765742
I also have a table called EMSX_Stage_cf765742
This table will have a header row and one or more data rows
This table will most likely have more header rows than StagingChain table, and that's all ok and not an issue for us.
There also appears to be a defined range called EMSX_STAGE_INSERTROW
This named range will always be the last row in the table EMSX_Stage_cf765742
What we want to achieve: .... the Rules....
1. Check in table StagingChain under column heading Action Trigger that the top (and only) data cell has the text TRUE ... if this is not TRUE then do nothing, otherwise if this is TRUE then ....
2. Validate that all column headings in table StagingChain have a similar column heading in table EMSX_Stage_cf765742 .... this is a validation for us and its only a one way requirement given that we are ok if table EMSX_Stage_cf765742 has more columns..... if false then pop alert to user about this, otherwise if TRUE then ....
3. Copy all of the data cells in the first row below the header row of table StagingChain (remember there should only be one row of data in this table) and then ....
4. Check each copied cell does not have any error values (perhaps no values beginning with # ... blanks are ok .... the data is constantly changing so I need to make sure we don't pick up any values that are not correct .... note, you may want to put this part in before step 3 as long as that definitely eliminates picking up wrong values) .... if this step = False then opt out of macro, otherwise if TRUE then ...
5. Paste Special the data values under the same corresponding header name in table EMSX_Stage_cf765742 and in the row defined as EMSX_STAGE_INSERTROW which should be the last row of that table.
6. If named cell EMSXAutoOnOff is equal to EMSX Engine On = Auto then continue monitoring for step 1 ... if FALSE then ... If EMSXAutoOnOff is equal to EMSX Engine On = Manual then only monitor when button is pushed for one instance ... if FALSE then macro is off..... I suppose Step 6 becomes Step 1!
My take on it ... I presume the monitoring stage is where we will need some funky code so that the spreadsheet doesn't freeze or sap resources ... perhaps the trigger to TRUE in step 1 needs a lot of attention? ... also, a macro flicking through sheets will be a user distraction so we want toavoid if possible.
thanks heaps in advance.
