Adapt VBA code (excel add-in): Manual selection --> Automatic

[CedricMattelaer]

Hello all,

I downloaded a VBA excel add-in here made by Samuel E. Buttrey, calling the freeware lp_solve program (information can be found here).

When I start the add-in I have to select two ranges at some point in the beginning. The add-in then calculates everything I need. It works fine most of the time but as I have to apply this add-in a vast amount of time I wondered whether I could automatize that, ie instead of selecting it manually, change the macro a bit so that the ranges I normally enter by hand are provided by the macro.
Concretely, I think that the code processing the variable range I enter in the first box is

Private Sub VarRef_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

and the second one is

Private Sub CostRef_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

The actual add-in starts with:

Private Sub TransportRun_Click()


Dim objOut() As Double, objVal As Double, constVec() As Double, solution() As Double
Dim solutionOut() As Double

Dim direction As Long, xCount As Long, constCount As Long, _
    intCount As Long, intVec() As Long, status As Long
'
' In the transportation problem, it's always a minimum. For now.
'
direction = 0
'
' Elementary error checking: Check that the solution exists and has the right length
'
If VarRef.Value = "" Then
    MsgBox "No variable range entered; try again"
    Exit Sub
End If
'
' Check size
'
soln = Range(VarRef.Value)


'
' Find out number of variables and number of constraints. The CostRef should be
' two columns wider and two rows longer than the VarRef
'

If CostRef.Value = "" Then
    MsgBox "Nothing entered for objective function coefficients -- and that's bad"
    Exit Sub
End If

objIn = Range(CostRef.Value)

yCount = UBound(objIn, 1) ' counts rows
xCount = UBound(objIn, 2) ' counts columns

etc.

I already tried to put something like what is shown underneath in the beginning of the matrix:

Dim Varref as Range
Set Varref = Range(Cells(624, 38), Cells(653, 53))

But then for example the evaluation of the statement

If Varref.Value = "" Then

results in an type mismatch.

Any of you excel masterminds got any ideas or suggestions?

Thank you in advance!

 

[wigi]

Hello

Why not contacting the creator of the code? He will know better than anyone else what can and should be changed for this situation.

Try:

soln = Range(Cells(624, 38), Cells(653, 53))

and comment out earlier lines of code.

Analogous for objIn.

 

[CedricMattelaer]

Thanks wigi!

I think that that is indeed where the variables have to be submitted.
Unfortunately, the program crashes with no other explanation than "link crashed with error 424". No information about this particular error can be found in the documentation or on the internet. I don't think this is a VBA error by the way.

I'll try some other things, see if I can find something

 

[CedricMattelaer]

Dear all,

I changed some things but still no solution in sight.

Is it possible to continue using the Varref variable?
Especially, I wondered whether I had to declare this if I used it and if yes, how.
I tried, for example, the String object but it resulted many errors.

Perhaps a bit of help as to the type of variable that varref might be: it is used in the beginning:
Varref.Value should result in "Model!$AL$624:$BA$653", I think.

at the end of the add-in code, the Varref variable is used again:

Range(VarRef.Value)(j, i) = solution(j + (i - 1) * yCount - 1)
Range(VarRef.Value)(yCount + 2, xCount + 2) = objVal

So, what type is VarRef? I think I need to continue using it because if I replace Range(VarRef.value) by soln, no solution is generated.


Any ideas?

 

[CedricMattelaer]

Quick Edit: The variable is of a RefEdit type. Now, is there a way to trick excel into believing that I entered a predefined range there?

Thanks in advance.

 

[wigi]

For instance, if it's a Range:

Set VarRef = Range("A1:B10")

Change to suit your sheets.

 

[CedricMattelaer]

Hello wigi,

Thanks for your feedback. Unfortunately, it isn't a Range.
Normally, the RefEdit is a string but I tried many things and can't seem to get it working.

 

[CedricMattelaer]

Hey,

Quick feedback, somehow it works now. Thanks for your help