Microsoft Visual Basic: Compile error: Sum or Function not defined

[Dmitry]

Hello
I receive:
"Microsoft Visual Basic
Compile error:
Sum or Function not defined" after I have run a macro. I recorded the macro.
The macro code is:
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 24.03.2006 by Dmitry Kopnichev
'

'
Range("J16").Select
SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
ByChange:="$F$4:$I$12"
SolverSolve
End Sub
All recorded operations work by itself. How to make the macro run the
operations?

 

[Andrew Taylor]

You need to add a reference to make VBA recognise the
Solver functions: in the VBA editor select Tools/References,
find "SOLVER" in the list and check it.

 

[Nigel]

Solver is not recognised by the standard Excel object model, add a reference
to SOLVER in your VB Editor.

Goto VB Editor (Alt-F11) , select Tools->References then check the SOLVER
option.- the SOLVER add in must be installed for this to be visible.

 

[Dmitry]

Thank you, Nigel!
When I record the macro I press the Enter button when Solver Results window
appears to close it before I click the Stop recording macro button, but the
macro does not close the window. How to make the macro close the window?

 

[Dmitry]

Thanks Nigel!
How to repeat the macro for a contiguous range of sheets in a Workbook? I
call the range contiguous because the range sheets tabs are contiguous in
the Workbook.
How to make the macro close the Solver Results window?

 

[Guest]

http://support.microsoft.com/default.aspx?scid=kb;en-us;843304
How to create Visual Basic macros by using Excel Solver in Excel 97

should help you do what you want.

 

[Dana DeLouis]

Not sure, but see if there are any ideas here that can help.
If not, please post back. :>)

Sub Demo()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
Sht.Activate
SolverReset
SolverOk "J16", 1, , "F4:I12"
SolverSolve True
Next Sht
End Sub

 

[Dmitry]

Thanks Tom Ogilvy!
"Tom Ogilvy" <[email protected]> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:

 

[Dmitry]

Thanks Dana DeLouis.
Does your code repeats for all worksheets in a workbook? I wrote "How to
repeat the macro for a contiguous range of sheets (not all sheets) in a
Workbook?" How to repeat the macro for selected sheets in a Workbook?

 

[Dana DeLouis]

How to repeat the macro for selected sheets in a Workbook?

Hi. That was for all sheets. Would any ideas here help for selected
sheets?

Sub Demo()
Dim sht
For Each sht In ActiveWorkbook.Windows(1).SelectedSheets
sht.Activate
' Solver here...
Next
End Sub

Sub Demo2()
Dim sht
For Each sht In Array("Sheet1", "Sheet3", "Sheet5")
Worksheets(sht).Activate
' Solver here...
Next
End Sub

 

[Dmitry]

Thanks Dana DeLouis for the codes.
Would I have to type in all the selected sheets names in the Demo2 macro?
The selected range includes 40 sheets.

 

[Dana DeLouis]

Hi. I'm afraid I don't understand, but I'll try to help. I am assuming you
want to run the Solver code on the same range of cells on 40 different
worksheets. Is that correct?

For this, I was pointing out the "SelectedSheets" property.
One can hold the Control key and select all 40 sheets.
Or, if the sheets are together, one can select the first sheet, hold the
Shift key, and select the last sheet.

Would I have to type in all the selected sheets names in the Demo2 macro?

Yes. If your sheets were scattered, with no pattern to their names, this
was another method.

How to repeat the macro for a contiguous range of sheets (not all sheets)

If the sheets are grouped together, this gives us another option.
Suppose the sheets are the first 40 worksheets.

Sub Demo3()
Dim ws As Long
For ws = 1 To 40
Worksheets(ws).Activate
'Solver Code Here...
Next ws
End Sub

If the worksheets that you want to run Solver on begin with a unique name,
here is another option.
Suppose all the sheets that you want to run Solver on begin with "Data",
(ie Data1, Data2, etc...)

Sub Demo4()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "Data*" Then
ws.Activate
'Solver Code Here...
End If
Next ws
End Sub

Hope some of these ideas will work for you problem. :>)

 

[Dmitry]

Dear Dana DeLouis,
Thanks for your explanations and codes.
Yes, I want to run the Solver code on the same range of cells on 40
different worksheets.
My sheets are with a pattern to their names: for example 95_06 or äô_09, two
digits or letters, the underline and two digits.

 

[Dmitry]

Dear Dana DeLouis,
What does (1) mean in Sub Demo()