Dynamic Data Calculation

Started by WilliamMcKinley, October 26, 2016, 03:30:29 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

WilliamMcKinley

Hi All,

Newbie coder here (aka I can't write it, but I can kind of manipulate it to try and come to a solution :)). I've been adjusting some code I found for creating dynamic calculations and could use some help. This drawing sums up shape data through various different levels of the sheet. Everything is working great except for the one field that I want to multiply the data contained on some shapes instead of sum. There are two shapes called "Correct & Accurate" and "Correct & Accurate 2". The first one calculated how many parts are made correctly and the 2nd one adds it to a different cell that I can use to calculate the global total calculation (Rolled Throughput Yield). The code is pulling the numbers correctly for the global total, but I cant figure out how to change the calculation from addition to multiplication. The ultimate result should be: 66.7% x 75% = 50.025%. Here's some snipets from the code directly related to the Rolled Throughput Yield calculation. I'll post the whole sheet in case anyone wants to take a look at the whole thing!:


' Compute and update global criteria values
Public Sub updateTotals()
   
    Debug.Print "----updateTotals----"
   
    Dim shp As Shape
    Dim parentContainer As Shape
    Dim memberID As Variant
    Dim ShapeNames As String
    Dim totalRTY As Double

    totalRTY = 0

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Scan all regular shapes on the page (excluding special shapes) and perform computations
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For Each shp In filteredShapes(ActivePage.Shapes)
           
                    ' Take FPY value into account in container's total
            If shp.CellExists("Prop." & rtyIndicatorProperty, 0) Then
                If shp.CellsU("Prop." & rtyIndicatorProperty).ResultStr(visNone) <> "" Then
                    totalRTY = totalRTY + shp.CellsU("Prop." & rtyIndicatorProperty).Result(visNone)
                Else
                    Debug.Print "Empty FPY value on shape " + shp.Name
                End If
            Else
                Debug.Print "Missing FPY property on box " + shp.Name
            End If
   
    Next

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Update global criterias info data boxes
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For Each shp In filteredShapes(ActivePage.Shapes)
           
        Set parentContainer = getParentContainer(shp)
        Debug.Print , "Parsing page-level shape: " & shp.Name
       
        If isCriteria(shp) And parentContainer Is Nothing Then
            Debug.Print "Updating global criteria: " & shp.Name
            Select Case shp.CellsU("Prop.criteriavalue.Label").ResultStr(visNone)
                Case "RTY"
                    shp.CellsU("Prop.criteriavalue") = totalRTY
            End Select
        End If
       
    Next

    Set shp = Nothing
    Set parentContainer = Nothing

    Debug.Print "----END updateTotals----"
End Sub



Anyone have any ideas?

wapperdude

Couple of points.
  1)  The code snippets don't show any "multiplication" from what I noticed.
  2)  This could all be done in the shapesheet.

Anyway, here's code that does multiplication of two values.  Basically, it uses two methods, choose whichever you prefer.  Method1 imports values, assigns to two variables, and does the math.  Method2 does the math directly with the variables, and assigns result to 3rd shape.

Attached file has the code plus shapesheet only example.  The shapesheet solution advantage is that it's truly dynamic.  Change value in either of 1st two shapes and 3rd shape changes instantly.  The code generated value only updates when the code is run. 

Double click either 1st two shapes to change their values.


Sub Math()
    Dim Num1 As Double
    Dim Num2 As Double
    Dim Num3 As Double
    Dim Num4 As Double
    Dim shp1 As Shape
    Dim shp2 As Shape
    Dim shp3 As Shape
   
    Set shp1 = ActivePage.Shapes(1)
    Set shp2 = ActivePage.Shapes(2)
    Set shp3 = ActivePage.Shapes(3)
   
    Num1 = shp1.CellsU("Prop.MyDat").ResultStr(visNone)
    Num2 = shp2.CellsU("Prop.Dat2").ResultStr(visNone)
    Num3 = Num1 * Num2
    Num4 = shp1.CellsU("Prop.MyDat").ResultStr(visNone) * shp2.CellsU("Prop.Dat2").ResultStr(visNone)

    Debug.Print Num1 & "   " & Num2
    Debug.Print Num3 & "   " & Num4
   
    shp3.CellsU("User.TotVal").FormulaU = shp1.CellsU("Prop.MyDat").ResultStr(visNone) * shp2.CellsU("Prop.Dat2").ResultStr(visNone)
    Debug.Print shp3.CellsU("User.TotVal").ResultStr(visNone)
   
End Sub



Wapperdude
Visio 2019 Pro

WilliamMcKinley

That works well! Would I have to adjust the code every time I add a sheet? Ideally I'd like to be able to have a shape that collects data, then a shape that totals it up. Is this possible with your method? That way I could create unlimited types of the data-collecting shape, the have various calculations in the total shape. The original sheet also had a handy feature where it would create totals inside containers and an overall global total.

Not sure if this is possible or not! Thanks so much for the help  :)

vojo

I imagine he will want the ability to add processes and such....add/delete actual processes and get them
in the sums probably better off with VBA.  Visio does not do peer shape discovery by shapesheet formulas

If static (always just 4 processes on a sheet), yes, easily done in shapesheet at shape and page levels.

wapperdude

@ Vojo:  right you are!   :D

Yes, possible with VBA.  Create a loop to look at all shapes.  Check each shape for data entry, e.g.,Prop.My Data, fetch the value and add to aggregate total.  Once all shapes checked, then do whatever needed to the new total.  You will need some error checking so code doesn't bomb if the entry doesn't exist.

Wapperdude
Visio 2019 Pro

WilliamMcKinley

It's good to know its possible, but unfortunately I have no idea where to begin haha! :o

vojo

BTW, June the Second did a whole bunch of intelligent shapes for something like this

One example of using his shapes:  Build up a radio receiver and see gain/loss per stage, feedback values, etc

Yacine

I've written some time ago a shape that handles arbitrary formulas with references to other shapes.
You may want to have a look: http://visguy.com/vgforum/index.php?topic=1714.0
Yacine

vojo

So here is an idea that I admit I have not thought thru...but maybe something here

- Treat the page as sort of a calculator
     - E.g. say 4 user cells at page level that act like registers:   AX, BX, CX, DX   maybe even a STACK
     - Maybe even N sets of registers and let the shapes use/move between the sets (sort of like a context switch).
- Each shape could
     - pull from registers
     - Do something (even the user defined functions approach)
     - push to registers (same or new)
- The trick would be to figure out deterministically the order the shapes evaluate their cells
     - Maybe DEPENDSON
     - Maybe something sort of like a "program counter" at the page level
     - This would be the tough part...one would think visio would go thru shapes based on sheet.<xx> order but
       I don't believe that is guaranteed.  I believe its more of a spatial or path of "changed cells" events
     - (you can control the order of cells within a shape with DEPENDSON).

Just a thought