Hello everyone, I’m currently using an Excel VBA macro to solve an optimization model in which the solver loops “i” times in order to get an optimal result. Here’s the thoroughly documented code:
'Declares the macro procedure.
Sub squad_optimizer()
'Declares i as an integer and prepares the loop to execute 48 times
Dim i As Integer
For i = 0 To 47
'Sets the value of the $Team_Picker.B3:B12 column to be equal to the value of the $Team_Picker.B65:B74 column oftfseted "i times to the right". So at the first run (i=o) it will take the value from $Team_Picker.B65:B74, however at the second run (i=1) the value from $Team_Picker.C65:C74 will be taken, on the third run (i=2) $Team_Picker.D65:D74, and so on.
Sheets("Team_Picker").Range("$B$3:$B$12").Value = Sheets("Team_Picker").Range("$B$65:$B$74").Offset(0, i).Value
'Invokes the excel solver addon (Open solver is used here since the native Excel solver has a limit of 200 cells for variable cells), and tells it to run without user intervention (to avoid pop-up windows). I'm gonna append my solver settings at the end of the post, since I don't know if you need to configure the solver or if it can run with the gui parameters when using it from Libreoffice basic.
OpenSolver.RunOpenSolver , False
'Sets the value of $Team_Picker.B75 offsetted i times to the right (Same thing like before. B75, on the first run, C75 on the second, D75 on the third, etc) to the value of $Team_Picker.BC61 (this is the objective cell in the solver)
Sheets("Team_Picker").Range("$B$75").Offset(0, i).Value = Sheets("Team_Picker").Range("$BC$61").Value
'Proceeds to the next "i" iteration
Next i
'Ends Macro
End Sub
I would love to use this macro on LibreOffice, since I do most of my work on a Linux laptop, but I know very little programming to transform this VBA macro into LibreOffice Basic. So if anyone is able to convert this script into LibreOffice Basic I would appreciate it a ton.
Here are my solver settings:
And here’s the spreadsheet of my model if anyone is interested on testing the solution:
I would appreciate any help, feel free to ask me any question.
Edit: You can check at https://www.reddit.com/r/libreoffice/comments/1d5slku/help_converting_a_vba_solver_loop_macro_for_use/, where I also posted the same question. Unfortunately, I have no asnwer as of 01/06/2024.