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
### 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
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