Originally posted by: BL_G

Hello,

I am using CPLEX 12.2.0.2 and Microsoft Excel 2007 and I am experiencing problems with the VBA functions for CPLEX. The files cplex122.xll and cplexvba.bas are installed / added to Excel.

Even though the Excel GUI works fine, I am receiving following 2 errors (also at the 3 excel VBA examples provided with the software):
1. at first it tells me that the formula is not correct
2. then I receive the message: "Failed to setup optimization problem. Error 22: No variable cells defined".

How can I solve this problem?

Thanks,
Bernard

I also created my own example (one objective, 2 constraints, which gives the same error messages:

Sub start()
Dim rngVar As Range
Dim rngCon As Range
Dim rngConUb As Range
Dim rngObj As Range

Set rngVar = Worksheets("Tabelle1").Range(Tabelle1.Cells(7, 2), Tabelle1.Cells(16, 2))
Set rngCon = Tabelle1.Range("e18:f18")
Set rngConUb = Tabelle1.Range("e3:f3")
Set rngObj = Tabelle1.Cells(18, 2)
cplexvba.CPXclear
cplexvba.CPXsetObjective ObjCell:=rngObj, Sense:=1
cplexvba.CPXaddVariable Variable:=rngVar, Lb:=0, Ub:=1
cplexvba.CPXaddConstraint Constraint:=rngCon, Ub:=rngConUb

cplexvba.CPXsolve
End Sub
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: SystemAdmin

I just tried here and everything works fine.
When you use the GUI, can you load and run the examples shipped with CPLEX successfully? Or does this also produce errors?
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: BL_G

Thank you for your reply.

The GUI examples work fine. I just get the error message for the VBA code.

The CPXaddVariable gives me the Error 22 message,
The CPXsetObjective yiels a formula error
The CPXclear works.

I am using the German version of CPLEX and Excel, but I think this shouldn't be any error source.

Thanks a lot!
Bernard
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: BL_G

Hallo Daniel,

I guess I found a workaround for this problem. Two problems exist:
1. my Excel version is German (changing to English only yielded a formula parsing error)
2. the Excel formulas in the European version use a semicolon instead of a comma as English Excel version do. I changed that in the Win XP regions settings. This corrected the formula parsing error.

I hope this error will be corrected in one of the next bugfixes. I don't think other European users get the Excel VBA to work otherwise.

Thanks,
Bernard
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: SystemAdmin

Thanks to figuring out the problem. I will see to a fix.
Just to make sure I understood correctly:
Changing the language to English and changing the separator from ',' to ';' fixes all the problems you mentioned?
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: BL_G

Correct, changing Excel to English AND then changing the separator. Yet, I changed the separator from ";" (European Version) to "," (English Version). This was done in the windows regions environment.
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: FidanBoylu

Hi there,
I am getting the following error:
Failed to setup optimization problem Error 24: Cannot convert value to number.

I am using Excel 2010 with cplex solver 12.2.

can you please help?
thanks
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: SystemAdmin

This error occurs when the CPLEX Excel connector attempts to parse a cell as a number but fails to convert the cell's content to a number. This may happen because the cell is empty, contains a string (that does not represent a number), etc.
Do you get a row/column index for the cell with the error message?
If you don't get that then it might be an option to disable constraints one by one and see which of them produces the error. From there it should be easy to find the offending cell.
Do you use anything else but numbers in your sheet? Is the model and all data contained in the same sheet? Or is it spread over multiple sheets?
Could you attach your sheet here?
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: UJT

Did you solve this problem?

I have this problem now.

Can you help me?

THX !

#CPLEXOptimizers
#DecisionOptimization

Originally posted by: BL_G

Hello,

I was just wondering if there are any news concerning the German/European Excel versions and CPLEX error 22? One of my students is currently having problems using CPLEX 12.4. with a German Excel 2007 version. Switching Excel 2007 to English was actually not that easy, since she didn't have the "english language pack".

Is there any workaround possible for German / European Excel 2007 versions without the English language pack?

Thanks for any reply.

Bernard
#CPLEXOptimizers
#DecisionOptimization

Originally posted by: SystemAdmin

Sorry, no news :-(
I have added your feedback to the internal data base item that tracks this known limitation.
#CPLEXOptimizers
#DecisionOptimization