Create kind of virtual cell(range)

[Jee-Bee]

Hello I found a Excel tool for creating and updating a DSM matrix(Design structural Matrix) on (Excel Macros for partitioning und Simulation – The Design Structure Matrix (DSM) - Here is the original file be found. I’m not able to add it because the file is to big with 1.3 MB).
Since I use LibreOffice I want to convert it to LibreOffice so I can use it myself. This is my first time I ‘program’ something in Basic. So I run in some troubles.

First a small explanation what the the macro should do in general.

This is the initial situation of the files, both the list and the matrix.

Now I update the list by adding a new item between two existing ones.

If now pressing on the button “Update DSM” the DSM Matrix in Excel it be updated to this:

As you can see the values (‘1’) from Element 9 to Element 20 are shifted a row from element 9 and 1 column from element 9.

This is code that performs some actions in Excel what I’m able to achieve too in LibreOffice.
I put it here for reference

    ' saving previous DSM interdependency data
    Dim oldElementNum As Integer

    Worksheets("DSM").Select
    oldElementNum = 250
    Rem check current highest item number

    For i = 1 To 250
        If IsEmpty(Cells(i + 1, 2)) = True Then
            oldElementNum = i - 1
            Exit For
        End If
    Next i

    ' create temporary (virtual) DSM matrix
    ReDim tmpDSM(oldElementNum + 1, oldElementNum + 1)

    ' fill temporary DSM matrix with previous filled values
    For i = 1 To oldElementNum
        For j = 1 To oldElementNum
            If (Cells(i + 1, j + 2) = 1) And (i <> j) Then
                tmpDSM(i + 1, j + 1) = 1
            End If
        Next j
    Next i

The trouble is in this piece of the macro.

    ' updating elementNum
    elementNum = 250
    Worksheets("Elements Info").Select. ' First sheet

    For i = 1 To 250
        If Cells(i + 1, 1) = 251 Then
             elementNum = i - 1
             Exit For
        ElseIf IsEmpty(Cells(i + 1, 1)) = False Then
            tmpDSM(Cells(i + 1, 1) + 1, 1) = i + 1  ' The first row and column of tmpDSM indicate which Excel row and column
            tmpDSM(1, Cells(i + 1, 1) + 1) = i + 2  ' correspond to each DSM row and column AFTER the new changes applied
        End If
    Next I

The ### is the number 251. If the loop sees that this value is found it stop performing the loop.
The part I have difficulties with is the part in the ElseIf.
If the cell is empty (means new row is added) than it shift the values that in the tmpDSM(generated in the reference) to new positions See figure ‘DSM Matrix updated’
This should be converted I guess to something like this:

eish = doc.Sheets.getByName("ElementsInfo")
ReDim tmpDSM(oldElementNum + 1, oldElementNum + 1)

For i = 1 To 250
    If eish.getCellByPosition(i + 1, 1).Value = 251 Then
        elementNum = i - 1
        Exit For
    ElseIf eish.getcellByPosition(i + 1, 1).Type = 0 Then
        ' Shift the existing values from current >= index position a row/ column '
        tmpDSM(#Something must happen here#) = i + 1  ' The first row and column of tmpDSM indicate which Excel row and column'
        tmpDSM(#Something must happen here#) = i + 2  ' correspond to each DSM row and column AFTER the new changes applied'
    End If
Next i

I have the idea it is a kind of virtual cell range but I don’t have a clue how to do this in LibreOffice where everything is related to a cell

Thanks

[Lupp]

Would you be so kind, please, to explain what a DSM is supposed to be?
Diagnostic and Statistical Manual` of Mental Disorders probably?

If you are talking of a design structure matrix:
What is the primary representation?
I’m tired of “translating” silly Excel macros. Knowing the actual task I might be able to either write a clearly considered solution in a few minutes in such a case - or to decide that it isn’t my cup of tea.
Once again: How are the dependencies represented in your 250 original rows?
In what way shall the range be passed to the Sub or Function?
Is th result expected to be in “North American Format” or in the transposed format preferred elsewhere probably?

[Jee-Bee]

Yes it is a Design Structure Matrix. Sorry I should have that make more clear! I understand that for you it is faster to program the functions yourself. For me it’s not. Basic in LibreOffice Calc gives me hard times specially before i find the “Macro Guides”.
For my feeling ‘simple functions’ for setting textcolor was hard to find
I updated my question I hope this gives some more clues

[Lupp]

A second time:
Once again: How are the dependencies represented in your 250 original rows?
You surely won’t expect me to “reverse engineer” the answer from the incomplete code you posted.
And, to be clear: If I try to help somebody to get fixed a problem related to “macros”, I either will supply/suggest reasonable, well structured code - or none at all.
In many cases where I had complete VBA code charged with a task at my disposition, it was neither reasonable nor well structured.
Quoting @Jee-Bee: “I updated my question I hope this gives some more clues”
For me it didn’t. I’m probably too silly… I can’t change that.
Therefore: "How are the dependencies represented in your 250 original rows?
Make available a sample sheet. I might understand then without too many additional explanations.

[Jee-Bee]

I edited the question again. If you don’t understand what I’m meant now than I guess I’m unable to make clear my problem

[Lupp]

Sorry to say: You never tried seriously.
I explicitly asked you in what way the original Data (the “Design Structure” which is a kind of graph in the sense of graph theory surely) were represented.
Now you attach a few square-miles of images and demonstrate a new element inserted which not is part of any relation (“influence”, “dependency” or what words you prefer).
There’s nothing in all that stuff giving a clue for what reason there (e.g.) is a “1” in 5 x 8, but nothing in 3 x 6.

[Jee-Bee]

How to fill the graph is for me a manual process. I never talked about graph theory or what so ever.
I have aways talked about a (virtual )matrix from what the information inside could be shifted.

[Lupp]

This is not an answer.
I consciously misuse the answer tool to simply bump bump.

When I wrote my question in return commenting on posts I didn’t get a relevant answer. However, I don’t like the idea to simply have wasted time.

The question seems to be about the conversion of a rather simple type of graph from one given representation to a representation by a Design Structure Matrix.

Anybody who wants to help with the task would need to know how the primary representation was.
It cannot be too complicated to tell that or simply to give a sufficiently clear example.