RE: RE: [Help-glpk] VBA/dll for Excel

[Meketon, Marc]

Xypron:

Thank you for the work that you did on this.  I ran out of time to test
it a few weeks ago, but was able to do so this weekend (it's amazing how
much time your have when flying from Melbourne, Australia to Newark, New
Jersey via Kuala Lumpur and Stockholm!)

So far, I've managed to get it working.  But there are two tricky
points, one of them might just be a compilation setting.  I thought
sharing these points might help others trying to use GLPK from VBA.

(1) To control the simplex there is a control parameter structure called
smcp.  Similarly for controlling the integer programming routine there
is a control parameter called iocp.  Focusing on iocp, you can almost
duplicate this structure in VBA by using a "type" statement (I left off
the last few lines):

Type GLPK_INTOPT_PARM
    msg_lev     As Long    'message level
    br_tech     As Long    'branching technique
    bt_tech     As Long    'backtracking technique
    filler1     As Long    '==>for data alignment
    tol_int     As Double  'tol_int
    tol_obj     As Double  '
    tm_lim      As Long
    out_frq     As Long
    out_dly     As Long
    cb_func     As Long    'really the address of a callback function
    cp_info     As Long    'really the address of an information
structure
    cb_size     As Long
    pp_tech     As Long
    filler2     As Long    '==>for data alignment
    mip_gap     As Double  'data alignment problems
    mir_cuts    As Long
    gmi_cuts    As Long
    foo_bar00   As Double
    ...
End Type

Notice that there are two fields called "filler1" and "filler2".  I
needed to force tol_int and mip_gap to be on an 8-byte boundary.  I
wonder if there is a compiler setting to have the elements in a "struct"
be aligned on 4-byte boundaries instead of forcing doubles to be aligned
on 8-byte boundaries?

Or perhaps in a later release these structures could be reorganized to
have the doubles come first to ease the interoperability with other
languages.

(2) The other thing to note is that for some reason, VBA does not allow
the second statement below:

    Dim iocp as GLPK_INTOPT_PARM
    iocp.cb_func = AddressOf MyCallBackFunction  '==>Fails in
compilation

And the workaround is to set it via a subroutine:

    Public sub setCBFunc(iocp as GLPK_INTOPT_PARM, addressOfCBFunc as
long)
        iocp.cb_func = addressOfCBFunc
    End sub

And then use
    setCBFunc iocp, AddressOf MyCallBackFunction

instead of the assignment statement.

-Marc

-----Original Message-----
From: glpk xypron [mailto:address@hidden 
Sent: Tuesday, March 18, 2008 11:03 PM
To: Meketon, Marc; address@hidden
Subject: Re: RE: [Help-glpk] VBA/dll for Excel

Hello Marc,

The following module correctly shows a collected message until GLPK
aborts the program due to the nonexistent column, using the library in
ftp://glpk.dyndns.org/glpk/glpk_4_27.dll.stdcall.zip
(see my prior mail).

Attribute VB_Name = "TestGLPK"
Option Explicit

Public Declare Function glp_create_prob Lib "c:\temp\glpk\glpk_4_27.dll"
() As Long
Public Declare Sub glp_set_prob_name Lib "c:\temp\glpk\glpk_4_27.dll"
(ByVal lp As Long, ByVal name As String)
Public Declare Sub glp_delete_prob Lib "c:\temp\glpk\glpk_4_27.dll"
(ByVal lp As Long)
Public Declare Function glp_get_prob_name Lib
"c:\temp\glpk\glpk_4_27.dll" (ByVal lp As Long) As Long
Public Declare Sub glp_term_hook Lib "c:\temp\glpk\glpk_4_27.dll" (ByVal
func As Long, info As Long)
Public Declare Sub glp_set_col_name Lib "c:\temp\glpk\glpk_4_27.dll"
(ByVal lp As Long, ByVal j As Long, ByVal name As String)
Private Declare Function SysAllocStringByteLen Lib "oleaut32" (ByVal
pwsz As Long, ByVal length As Long) As String

Public Function MessageHandler(ByVal info As Long, ByVal msg As Long) As
Long
  Static collect
  Dim mess As String
  mess = SysAllocStringByteLen(msg, 1024)
  mess = Left$(mess, InStr(mess, Chr$(0)) - 1)
  collect = collect & mess
  MsgBox collect
  MessageHandler = 0
End Function


Sub test()
  Dim i    As Long
  Dim l    As Long
  Dim lp   As Long
  Dim info As Long
  Dim name As String
  
  lp = glp_create_prob()
  Call glp_term_hook(AddressOf MessageHandler, info)
  name = "MyProblem"
  Call glp_set_prob_name(lp, name)
  name = "nonsense"
  Call glp_set_col_name(lp, 100, name)
  name = ""
  l = glp_get_prob_name(lp)
  name = SysAllocStringByteLen(l, 512)
  Call glp_delete_prob(lp)
End Sub


-------- Original-Nachricht --------
> Datum: Tue, 18 Mar 2008 12:49:35 -0400
> Von: "Meketon, Marc" <address@hidden>
> An: address@hidden
> CC: "Xypron" <address@hidden>
> Betreff: RE: [Help-glpk] VBA/dll for Excel

> Thanks.
> 
> I produced a DLL as well by adding __stdcall __export to the right
> function calls.  [BTW, I'm using the free Turbo C++ compiler from
> Borland.]
> 
> BUT, I cannot seem to get the callback function for "glp_term_hook" to
> properly work.  Before I begin to setup the problem, I use very
similar
> VBA code to what the Version 10 of the Informatiks software uses:
> 
>   Const MAXMSG as long = 500
>   Private ActMsg as long
> 
>   Private Messages(MAXMSG) as string
> 
>   Public sub INIT_GLPK()
>     Dim info as long
> 
>     info = 0
>     glp_term_hook AddressOf MessageHandler, info
>     ActMsg = 0
>   end sub
> 
> and
> 
>   public function MessageHandler(ByVal info As Long, ByVal msg As
Long)
> As Long
>     Dim l As Long
>     Dim Err_Message As String
> 
>     On Error Resume Next
> 
>     If ActMsg < MAXMSG Then
>         ActMsg = ActMsg + 1
>     End If
>     l = lstrlen(msg)
>     Err_Message = Space$(l + 1)
>     lstrcpy Err_Message, msg
>     Err_Message = Left(Err_Message, l)
>     Messages(ActMsg) = Err_Message
> 
>     MessageHandler = 1
>   End Function
> 
> However, when the "xputs" function in GLPK tries to return, the code
> bombs.  The "xputs" does call the "MessageHandler", and it seems to
pass
> in the correct string, and xputs does see the correct return.  I
believe
> that somehow the stack gets messed up a bit, but I haven't figured out
> any workaround.
> 
> Any ideas on how to successfully invoke the call-back function for the
> terminal hook?
> 
> -Marc
> -----Original Message-----
> From: Xypron [mailto:address@hidden 
> Sent: Tuesday, March 18, 2008 10:53 AM
> To: Meketon, Marc
> Cc: address@hidden
> Subject: Re: [Help-glpk] VBA/dll for Excel
> 
> Hello Marc,
> 
> couldn't You use the DLL produced by
> w32/Build_GLPK_with_VC6_MT_DLL.bat
> with Visual C++ 7.0 or 6.0?
> (see ftp://glpk.dyndns.org/glpk/glpk_4_27.dll.tar.gz)
> 
> Best regards
> 
> Xypron
> 
> Meketon, Marc wrote:
> >
> > A while back there was a version of GLPK for VBA applications (such
as
> 
> > in Excel). That was based on version 10 of GLPK. Does anyone work
with
> 
> > GLPK in VBA with a recent version of the software? If so, can you 
> > please provide links? It appears that the original folks (Informatik

> > Fuchs) do have a version 23, but it's on a CD for 50 euros, and
there 
> > is no English page for it, so I'm not sure how possible it will be
for
> 
> > me to get the CD.
> >
> > FYI, the German web page for the version 10 VBA implementation is: 
> > http://www.informatikfuchs.de/Software/GLPK/glpkVBDownload.htm (and 
> > there is a link for the English version of this web page on this
> page).
> >
> > -Marc Meketon
> >
> >
>
------------------------------------------------------------------------
> ---- 
> >
> > This e-mail and any attachments may be confidential or legally 
> > privileged. If you received this message in error or are not the 
> > intended recipient, you should destroy the e-mail message and any 
> > attachments or copies, and you are prohibited from retaining, 
> > distributing, disclosing or using any information contained herein. 
> > Please inform us of the erroneous delivery by return e-mail.
> >
> > Thank you for your cooperation.
> >
>
------------------------------------------------------------------------
> ---- 
> >
> >
>
------------------------------------------------------------------------
> >
> > _______________________________________________
> > Help-glpk mailing list
> > address@hidden
> > http://lists.gnu.org/mailman/listinfo/help-glpk
> >   
> 
>
------------------------------------------------------------------------
----
> This e-mail and any attachments may be confidential or legally
privileged.
>  If you received this message in error or are not the intended
recipient,
> you should destroy the e-mail message and any attachments or copies,
and
> you are prohibited from retaining, distributing, disclosing or using
any
> information contained herein.  Please inform us of the erroneous
delivery by
> return e-mail. 
> 
> Thank you for your cooperation.
>
------------------------------------------------------------------------
----

-- 
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
---------------------------------------------------------------------------- 
This e-mail and any attachments may be confidential or legally privileged.  If 
you received this message in error or are not the intended recipient, you 
should destroy the e-mail message and any attachments or copies, and you are 
prohibited from retaining, distributing, disclosing or using any information 
contained herein.  Please inform us of the erroneous delivery by return e-mail. 

Thank you for your cooperation.
----------------------------------------------------------------------------