Can you have automatic recalculation of Excel trigger a routine in VBA?

Navigation
2

I have a graphic scheme in Excel that is done by means of conditional formatting (see figure below), that is, in colored cells there is no content, only the background colors are changed based in values from other cells.

ThedashedbluelineismadebyaroutineinVBA(theconnectionsarecomplextomakethebordersalsobyconditionalformatting),itoccursthattheuserafterreleasingthevalueswhentheyneedtobechanged,thefiguresareupdateddynamicallybyExcelitself,butthedashedlinerequiresabuttontobepressedforVBAtoredotheconnections(whichisnatural)thereisavisualdiscrepancybetweenthedataandgraphicalresultspresenteduntiltheroutineistriggered.

Thisunderminestheverydataentrythatdependsonthedesiredresultofwhatisbeinganalyzedgraphically.Thefigurebelowshowsthissituation,thedisconnectionofthedashedlinethatisnotdynamicallychanged.

Thenextfigureshowstheresultaftertheroutineistriggeredbythebutton.

The routine resets the connections immediately without waiting time. What I could not find is a way for the routine to be dynamically triggered as Excel resumes the calculations automatically for any changes that are made to the worksheet.

The Application.Calculate triggers the calculation in Excel (idem type F9 ) from VBA , what I need is the inverse, Excel to trigger the VBA routine.

Is it possible?

    
asked by anonymous 18.04.2017 / 23:20

3 answers

2

Use the Calculate event for the worksheet on which the button and graphic are, as follows: 

Private Sub Worksheet_Calculate()

    Call Sub_Usada_no_Botao

End Sub

So every time some calculation occurs in your spreadsheet, Sub will be triggered.

    
19.04.2017 / 01:50
2
Unfortunately, you do not have a simple way to do this; it is necessary to break the head and create an on / off system of the Application.Calculation feature. The modes for this feature are: 

Application.Calculation = xlCalculationAutomatic     'Cálculos automáticos
Application.Calculation = xlCalculationManual        'Cálculos automáticos desativados
Application.Calculation = xlCalculationSemiautomatic 'Cálculos automáticos, menos em tabelas

One suggestion I give you is to apply the following idea in the worksheet module that you change the data and displays the 'graphic': 

Private Sub Worksheet_Activate() 
    Application.Calculation = xlCalculationManual
End Sub 

Private Sub Worksheet_Deactivate() 
    Application.Calculation = xlCalculationAutomatic 
End Sub

So when you select the worksheet with the chart, VBA will run the Activate event, turning off the automatic calculation, and it automatically restores itself when you access any other worksheet. To complement the answer to your question, simply add a line with Calculate in the code that runs with your button to see all graphic changes simultaneously at the click of the button. Put at the beginning of the code to get the results of the calculations before you manipulate the dashed edges.

Obviously, you can think of other ways to apply this feature, but keep in mind that it ALWAYS applies to all Excel (all open files).

    
19.04.2017 / 00:57
0

The same can be done to trigger a routine only when there is change in one or more cells specified in a Range.

Based on one of the answers to the question #

Private Sub Worksheet_Change(ByVal Target As Range)

Dim AlterouNaFaixaDeDados As Range

 'Indique na Range a faixa que queira acionar uma rotina dinamicamente se houver alteração nas suas células
 Set AlterouNaFaixaDeDados = Application.Intersect(Range("B2:E5"), Target)

 'Se houve alteração em uma ou mais células desta Range, a rotina será acionada
 If Not AlterouNaFaixaDeDados Is Nothing Then

   'Coloque aqui a rotina a ser acionada
   Call Sub_Rotina

 End If

End Sub

    
19.04.2017 / 04:00
Assign Tuple return in two variables Laravel Authentication 5.4