Need an Event for Data Tables

[nbarczak]

Hi all,

I have developed a set of extensive macro calculations that I now want to trigger when a data table is created. These calculations have numerous inputs, and the idea is that the user should be able to use the data table function in Excel to vary any one of these inputs and record the result. The macros I have created are really a small program, with combinations of functions, subs, and custom objects. The problem is that I need some way for the creation of a data table to trigger the calculation of these macros for every entry in the data table.

Any ideas? I have all the calcs setup so that a single sub triggers everything. I originally thought to use the worksheet_change() event and trigger the calcs that way (with an option to turn off the calculations when not using data tables). Two problems with that. First, I don't know where the user will create the data tables. Very likely, they will put them on a separate worksheet. Second, the generation of data tables does not trigger the _Change() event. I also tried using the Worksheet_Calculate() event, but the data table did not trigger that either.

Help please.

 

[Legacy 98055]

Would there be any problem with intercepting the initial calculation? The datatable must fire the calculation event initially. Correct? Not on creation but on first calculation...

 

[nbarczak]

That could probably work. I tried it with a simple case, and it is definitely triggering the calculate event. And I think that I can use the workbook object for the calculate event. That should catch the calculation no matter where they put the data table.

But as I understand it, any code for an event is executed after the event. So its possible that the data table would trigger the calculate event and get the resulting value before my code has executed and updated values.

 

[Legacy 98055]

Oh. I see. I obviously don't know the scope of the complexity involved with what you are trying to do. I have not worked with datatables before. That's why this post interested me. I don't know precisely where or when you need to manipulate the table with your code. That is, at what point during the creation process and how you will determine the range to manipulate. The best I can offer without some really "interesting" code would be something akin to "BeforeDataTableInsert". In other words, we can intercept the creation of a data table using code such as this in the workbook module:

Private Sub Workbook_Activate()
    Application.CommandBars.FindControl(, 862).OnAction = Me.CodeName & ".BeforeDataTable"
End Sub

Private Sub BeforeDataTable()
    MsgBox "Run Your Code Here..."
    Application.CommandBars.FindControl(, 862).OnAction = ""
    Application.OnTime Now, Me.CodeName & ".DataTableDefaultAction"
End Sub

Private Sub DataTableDefaultAction()
    With Application.CommandBars.FindControl(, 862)
        .Execute
        .OnAction = Me.CodeName & ".BeforeDataTable"
    End With
End Sub

Private Sub Workbook_Deactivate()
    Application.CommandBars.FindControl(, 862).OnAction = ""
End Sub

Let me know if you need precision beyond this. It will be difficult to do more because the data table dialog in modal which means all typical vba code stops...

 

[nbarczak]

That is an interesting solution. But I dont' think it will work. If I understand your code correctly, this would only trigger an event once on the creation of the data table. And I need it to trigger for each calculation of a value in the data table.

I'm thinking the easiest way may be to just create my own array function that reproduces the effects of a data table but adds in a call to my calculations routine.

Public Function CustDataTable(in1 as Single, in2 as Single) as Single
 
Redim CustDataTable(appropriate size)
 
For i = each row entry
For j = each column entry
Enter inputs into cell values
 
Call ResultsCalculation
 
Record results in CustDataTable(i,j)
Next
Next
 
End Function

<CODE>
</CODE>

After writing two new classes and more functions and subs than I can count, one more function seems a small hassle. And then I can be sure it behaves how I expect.

 

[Legacy 98055]

I agree with you. One step further would be to create your own dialog if neccesary. Hoping that it's not...