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

CedricMattelaer

New Member
Joined
Jun 16, 2011
Messages
37
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
Code:
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
Code:
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:

Code:
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:

Code:
Dim Varref as Range
Set Varref = Range(Cells(624, 38), Cells(653, 53))
But then for example the evaluation of the statement

Code:
If Varref.Value = "" Then
results in an type mismatch.

Any of you excel masterminds got any ideas or suggestions?

Thank you in advance!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:

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

and comment out earlier lines of code.

Analogous for objIn.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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:
Code:
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
For instance, if it's a Range:

Code:
Set VarRef = Range("A1:B10")

Change to suit your sheets.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,735
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top