The Problem (Q-score 9, ranked #119th of 303 in the Excel VBA archive)
The scenario as originally posted in 2015
I used excel solver to solve an optimization problem, and I am trying to replicate it in R.
I found many packages like optim, ROI etc., but it seems all of them only take a vector as the object to optimize and allow the variables to take any continuous value. In my case, I have a constraint matrix that also needs to be satisfied and my variables can only take binary values.
Here is problem I want to solve:
A-D are machines, 1-3 are tasks and the number in the first matrix is the value generated by using X machine to do Y task. The constraints are: A-D can do and only can do one task (cannot split); each task can be worked and only be worked by one machine.
Here is the code I am using:
par = rep(c(0,1),6)
mat <- matrix(c(9,10,11,4,5,10,1,3,5,7,5,4), nrow = 3)
fr <- function(x) {
y= matrix(x,nrow = 4)
sum(mat %*% y)
}
a = optim(par, fr)
Some questions: How can I optimize maximum, seems this function default optimize minimum? How can I add constraints into it? How can I limit to binary variables?
Why community consensus is tight on this one
Across 303 Excel VBA entries in the archive, the accepted answer here holds solid answer (above median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — solid answer (above median) (+10)
8-line Excel VBA pattern (copy-ready)
You need to construct a vector for the objective function and a constraint matrix, finally solving with one of the R LP solvers:
library(lpSolve)
costs <- matrix(c(9, 10, 11, 4, 5, 10, 1, 3, 5, 7, 5, 4), nrow=3)
nr <- nrow(costs)
nc <- ncol(costs)
columns <- t(sapply(1:nc, function(x) rep(c(0, 1, 0), c(nr*(x-1), nr, nr*(nc-x)))))
rows <- t(sapply(1:nr, function(x) rep(rep(c(0, 1, 0), c(x-1, 1, nr-x)), nc)))
mod <- lp("max", as.vector(costs), rbind(columns, rows), "<=", rep(1, nr+nc), binary.vec=rep(TRUE, nr*nc))
Now you can grab the solution and the objective function:
mod$objval
# [1] 27
matrix(mod$solution, nrow=nr)
# [,1] [,2] [,3] [,4]
# [1,] 0 0 0 1
# [2,] 1 0 0 0
# [3,] 0 1 0 0
Note that functions like optim are not well suited for this problem both because they don’t consider matrices of constraints and also because they cannot limit to binary variable values.
When to Use It — classic (2013–2016)
Ranked #119th in its category — specialized fit
This pattern sits in the 97% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.
What changed between 2015 and 2026
The answer is 11 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.