locked
VBA using Shared Library Office 2011 Mac RRS feed

  • Question

  • I am attempting to access code that resides in a shared library from PowerPoint VBA.
    I can access the shared library code from another application.
    my VBA code is as follows:
    Private Declare Function mymod CDecl Lib "\usr\lib\libmylib.dylib" Alias "myMod" ()
    Sub MyTest()
       Rem Hi
       mymod
    End Sub
    When I try to run MyTest, I receive 
    Run-time error '53':
    File not found: \usr\lib\libmylib.dylib
    libmylib.dylib contains only on function 
    void mymod();
    Two questions:
    1) is my Function declaration correct?
    2) Where is PowerPoint VBA expecting shared modules of this nature to reside.
    Thanks.
    Friday, August 19, 2011 11:22 AM

Answers

  • I wrote some small function in XCode for details see http://www.realsoftwareblog.com/2011/12/how-to-create-dylib-on-mac-os-x-and.html

    #include <string.h>

    double addFunctionC( double a, double b ) {
        return a + b;
    }

    double multFunctionC( double a, double b ) {
        double result;
        result = a * b;
        return (result);
    }

    int stringLengthC( char *str ) { return strlen(str);
    }

    and here the implementation in Mac Office 2011 VBA

    Private Declare Function addFunction _
    Lib "Macintosh HD:Users:philipp:Documents:Excel:lib:libSampleDyLib.dylib" Alias "addFunctionC" (ByVal a As Double, ByVal b As Double) As Double

    Private Declare Function multFunction _
    Lib "Macintosh HD:Users:philipp:Documents:Excel:lib:libSampleDyLib.dylib" Alias "multFunctionC" (ByVal a As Double, ByVal b As Double) As Double

    Private Declare Function stringLength _
    Lib "Macintosh HD:Users:philipp:Documents:Excel:lib:libSampleDyLib.dylib" Alias "stringLengthC" (ByVal a As String) As Integer


    Function Addition(a As Double, b As Double)
        Addition = addFunction(a, b)
    End Function

    Function Multiplicate(a As Double, b As Double)
        Multiplicate = multFunction(a, b)
    End Function

    Function myLength(a As String)
        myLength = stringLength(a)
    End Function

    hope this helps,

    • Proposed as answer by Peter Jamieson Monday, March 12, 2012 7:57 AM
    • Marked as answer by danishani Monday, March 12, 2012 9:39 PM
    Sunday, March 11, 2012 5:37 AM
  • Hi Peter,

    Mac OSX 10.6.8 XCode 4.0.2 and the active scheme is set to 32-bit mode. Mac Office 2011 is running in 32-bit. All other settings from Default. Header file is empty and no environment variable has been changed.

    I tested it in Excel for Mac 2011 Version 14.1.4. The parameters have to be passed By Value (byVal). Only having a full path to the library was working. I copied the dylib file from the builds folder location to ../Excel/lib/.

    Philipp


    • Edited by philipp_ba Sunday, March 11, 2012 8:51 PM
    • Proposed as answer by Peter Jamieson Monday, March 12, 2012 7:58 AM
    • Marked as answer by danishani Monday, March 12, 2012 9:39 PM
    Sunday, March 11, 2012 8:23 PM
  • Hi Philipp,

    > active scheme is set to 32-bit mode

    It was that. Hooray! Thanks very much for that - it has made my day.

    FWIW I think that putting the dylib in a folder in the relevant PATH allows you to avoid specifying the entire pathname in the Declare Function statement. I am not familiar with the normal search paths on Mac OS X but there is documentation at

    https://developer.apple.com/library/mac/#documentation/Darwin/Reference/Manpages/man1/dyld.1.html

    (not sure that is publically accessible).

    This suggests that the dynamic linker searches the folders in
    DYLD_LIBRARY_PATH

    then "the default locations for libraries"

    It also mentions that is searches

    DYLD_FALLBACK_LIBRARY_PATH (which defaults to $(HOME)/lib:/usr/local/lib:/lib:/usr/lib )

    "for libraries not found in their install path", i.e. the path defined in the XCode build instructions.

    I

     a. created ~/lib (i.e. "Macintosh HD:Users:philipp:lib" )

     b. put my dylib in there

     c. used 

    Private Declare Function addFunction _
    Lib "libSampleDyLib.dylib" Alias "addFunctionC" (ByVal a As Double, ByVal b As Double) As Double

    and that worked. I think ~/usr is actually one of the standard search locations for libraries, but I do not know for sure.


    Peter Jamieson

    • Proposed as answer by danishani Monday, March 12, 2012 9:39 PM
    • Marked as answer by danishani Monday, March 12, 2012 9:39 PM
    Monday, March 12, 2012 8:16 AM

All replies

  • Dear Don,

    Please see this kb article about the problem:

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/b060f291-0754-4e85-a7b9-e64259e6baad

    The problem ocurrs when the DLL is not the search path.

    Hope this helps.

    Regards,


    Be happy.
    • Marked as answer by Bruce Song Tuesday, August 30, 2011 7:17 AM
    • Unmarked as answer by danishani Monday, March 5, 2012 6:12 PM
    Monday, August 22, 2011 2:24 AM
  • Hi Don,

    How about the problem on your side? If you still need any assistant, just feel free to let us know.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 24, 2011 6:42 AM
  • This answer appears to point to the question thread! - does anyone have any idea which kb article referenced?

    Peter Jamieson

    Monday, March 5, 2012 4:47 PM
  • Hi Peter,

    I believe the KB article mentioned is the one below:

    http://support.microsoft.com/kb/240416

    Edit: I noticed you are using Office 2011 for Mac, so in that case this KB article will not be much of a Help I guess.

    You might ask the question at answers.microsoft:

    http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macpowerpoint

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.


    • Edited by danishani Monday, March 5, 2012 5:20 PM notice Mac
    Monday, March 5, 2012 5:09 PM
  • Hi Daniel,

    If you have received my previous response (now deleted), please ignore it!

    I was indeed trying to research a Mac Word VBA point and I suspect the original responder had not noticed it was a Mac question.

    After looking at this thread, I came across another post that suggests there is /a/ way to do something in at least some versions of Office 2011 VBA:

    http://delphihaven.wordpress.com/2011/10/17/firemonkey-forms-and-dlls/


    Peter Jamieson

    Monday, March 5, 2012 6:13 PM
  • And that approach does in fact work. How to do the same thing using XCode is a different question.

    Peter Jamieson

    Tuesday, March 6, 2012 7:50 PM
  • I wrote some small function in XCode for details see http://www.realsoftwareblog.com/2011/12/how-to-create-dylib-on-mac-os-x-and.html

    #include <string.h>

    double addFunctionC( double a, double b ) {
        return a + b;
    }

    double multFunctionC( double a, double b ) {
        double result;
        result = a * b;
        return (result);
    }

    int stringLengthC( char *str ) { return strlen(str);
    }

    and here the implementation in Mac Office 2011 VBA

    Private Declare Function addFunction _
    Lib "Macintosh HD:Users:philipp:Documents:Excel:lib:libSampleDyLib.dylib" Alias "addFunctionC" (ByVal a As Double, ByVal b As Double) As Double

    Private Declare Function multFunction _
    Lib "Macintosh HD:Users:philipp:Documents:Excel:lib:libSampleDyLib.dylib" Alias "multFunctionC" (ByVal a As Double, ByVal b As Double) As Double

    Private Declare Function stringLength _
    Lib "Macintosh HD:Users:philipp:Documents:Excel:lib:libSampleDyLib.dylib" Alias "stringLengthC" (ByVal a As String) As Integer


    Function Addition(a As Double, b As Double)
        Addition = addFunction(a, b)
    End Function

    Function Multiplicate(a As Double, b As Double)
        Multiplicate = multFunction(a, b)
    End Function

    Function myLength(a As String)
        myLength = stringLength(a)
    End Function

    hope this helps,

    • Proposed as answer by Peter Jamieson Monday, March 12, 2012 7:57 AM
    • Marked as answer by danishani Monday, March 12, 2012 9:39 PM
    Sunday, March 11, 2012 5:37 AM
  • Hi Philipp,

    Thanks for posting.

    I tried following that article, but could not get Office to find the dylib (it always fails on an error 53, no matter where I put it). Because the file is undoubtedly where I said it is, I have to assume that the message for error 53 is misleading and that Office VBA is finding the file, but failing to find something else.

    At first I assumed that Realbasic has a better implementation of the external function definition feature than VBA does, but if you can get it to work with VBA, there must be something else going on.

    The Realbasic article talks about using xCode 3, whereas I only have xCode 4 here. Are you using xCode 3?


    Peter Jamieson

    Sunday, March 11, 2012 7:17 PM
  • Hi Peter,

    Mac OSX 10.6.8 XCode 4.0.2 and the active scheme is set to 32-bit mode. Mac Office 2011 is running in 32-bit. All other settings from Default. Header file is empty and no environment variable has been changed.

    I tested it in Excel for Mac 2011 Version 14.1.4. The parameters have to be passed By Value (byVal). Only having a full path to the library was working. I copied the dylib file from the builds folder location to ../Excel/lib/.

    Philipp


    • Edited by philipp_ba Sunday, March 11, 2012 8:51 PM
    • Proposed as answer by Peter Jamieson Monday, March 12, 2012 7:58 AM
    • Marked as answer by danishani Monday, March 12, 2012 9:39 PM
    Sunday, March 11, 2012 8:23 PM
  • Hi Philipp,

    > active scheme is set to 32-bit mode

    It was that. Hooray! Thanks very much for that - it has made my day.

    FWIW I think that putting the dylib in a folder in the relevant PATH allows you to avoid specifying the entire pathname in the Declare Function statement. I am not familiar with the normal search paths on Mac OS X but there is documentation at

    https://developer.apple.com/library/mac/#documentation/Darwin/Reference/Manpages/man1/dyld.1.html

    (not sure that is publically accessible).

    This suggests that the dynamic linker searches the folders in
    DYLD_LIBRARY_PATH

    then "the default locations for libraries"

    It also mentions that is searches

    DYLD_FALLBACK_LIBRARY_PATH (which defaults to $(HOME)/lib:/usr/local/lib:/lib:/usr/lib )

    "for libraries not found in their install path", i.e. the path defined in the XCode build instructions.

    I

     a. created ~/lib (i.e. "Macintosh HD:Users:philipp:lib" )

     b. put my dylib in there

     c. used 

    Private Declare Function addFunction _
    Lib "libSampleDyLib.dylib" Alias "addFunctionC" (ByVal a As Double, ByVal b As Double) As Double

    and that worked. I think ~/usr is actually one of the standard search locations for libraries, but I do not know for sure.


    Peter Jamieson

    • Proposed as answer by danishani Monday, March 12, 2012 9:39 PM
    • Marked as answer by danishani Monday, March 12, 2012 9:39 PM
    Monday, March 12, 2012 8:16 AM
  • Hi Peter

    Excellent solution. I didn't wanted to use the standard locations for libraries and so I used the full path. Obviously I've missed the default to $(HOME)/lib.

    Thanks for your additional findings. They worked perfectly.

    Philipp



    • Edited by philipp_ba Monday, March 12, 2012 12:24 PM
    Monday, March 12, 2012 9:09 AM
  • Thanks, it helps a lot !

    I still have a bit issue. In the dylib, I want to build a function to return String like:

    char *tcpconnect()

    {

         return "running";

    }

    However, when I call the function inside VBA, I always receive a result like "running^^^^^^^^^^". I'm not an expert in C language so I don't really know how to fix this? Do you guys have any ideas?

    Thanks,

    David

    Thursday, March 22, 2012 6:28 AM
  • I've started a reply to your more recent message, .i.e. at

    http://social.msdn.microsoft.com/Forums/en-MY/isvvba/thread/aefce0a1-2b8e-45d7-b8e1-944f296a4292


    Peter Jamieson


    • Edited by Peter Jamieson Saturday, April 21, 2012 2:15 PM include better reference
    Thursday, March 29, 2012 2:17 PM
  • Hi Guys

    This thread is great!  Thanks for the great input so far but lets keep going.   What is the best way to exchange arrays between excel and the dylib?  I'm coming from the PC where I wrote xll's to do this using XLW and had thought it would not be possible on Mac since xll's are not supported.  Now I'm hoping I was wrong.

    I want my vba function to take a range or ranges and send them off to C for calculation and then return from C the modified array(s).   Any help or suggestions would be greatly appreciated.

    Thanks,

    Rick

    Friday, April 20, 2012 2:25 PM
  • Rick,

    In case you missed it, there is a little bit more in the thread mashix started - I've now referenced it properly in this thread, but it's here:

    http://social.msdn.microsoft.com/Forums/en-MY/isvvba/thread/aefce0a1-2b8e-45d7-b8e1-944f296a4292

    I haven't taken it any further than that yet, but my starting point would be as follows:

     a. on Mac, AFAIK, there is no C/Objective-C support for any of the COM/Automation stuff that we're familiar with on Windows.

     b. At the very least on the Windows side, there would be C/C++ support for the standard structures using in COM/Automation, primarily Variants, Variant Arrays, and arrays of Variants I suppose. Then there might be support for specific structures such as Excel Range objects.

     c. I don't really see why, in principle, if the structures used in Windows C/C++ are defined in .h files, they could not be ported and re-used in the Mac world. In practice, I have normally found that "between hard and impossible," but then I'm not a C programmer.. I also don't know what licensing issues exist, if any.

     d. If it's just too hard to port the necessary structure definitions, you would have to unpack your range data on the VBA side and send something more easily understood on the C side (i.e., either scalar data types or structures whose definitions you can describe in C, then reassemble any returned data.


    Peter Jamieson

    Saturday, April 21, 2012 2:30 PM
  • OK, now I want to throw a curve into this conversation. I stumbled across this old article about converting DLL from Windows to Mac. It is for PowerPC, so the information here needs to be updated for Intel. I can't find information about the author except that this was published on line 13 Jan 1997. I am hoping this might be useful.

    Michael Lutz on Porting DLL's to Macs

    The attached details some of the tips and tricks that I experienced porting a DLL written in Microsoft's visual c++ to the power pc and 68K series macs using Code Warrior Version 7.

    The DLL was called by a user-interface written in EXCEL 5 visual basic for applications. All data was entered in EXCEL and graphics and tables were created by VBA subroutines. The DLL was used for calculations. My goal was to get a DLL with a consistent calling scheme for PC, MAC and unix. The DLL itself performs numerically intensive operations: calculations associated with nonlinear and linear regression and modeling for different applications in pharmacology, biochemistry and enzymology. These calculations were too slow when performed in VBA or in a visual basic program. The DLL (or code resource (68K mac), code fragment (power pc)) only contained the calculation code. All of the UI was written in VBA.

    Tips, tricks and quirks For 68K macs

    1) Code Warrior: Was successful with settings: code resource, large code model68K alignment, no optimization. Resource creator was RSED, type rsrc, restype CODE, standard header, extended resource was checked.

    2) The default size of data items is different between VBA and code warrior. To use Integers and Doubles in VBA, check 8 byte doubles in code warrior and do not check 4 byte ints in code warrior. When you change these, make sure you change the libraries that you link with. Code warrior uses a naming scheme like 2i/8d for each library (meaning 2 byte integers, 8 byte doubles). I linked with MACOS lib, MathLib68K (2i/8d).A4.lib, ANSI (2i/8d).A4.68K.lib and console.stubs.c.

    3) Pascal calling conventions must be used, the compiler seems to be very particular about this (meaning some forms that I thought should work didn't). I used

    pascal int main(. .... )

    where .... is a bunch of arguments, some by reference, others by value. Basically all parameter passing methods seemed to work. Be careful about 0 versus 1 based indexes in visual basic for applications.

    4) You have to preserve and restore the A4 state. Make sure that you do this when you first enter the routine and don't attempt to do it again in your code!!!

    5) Segmentation was an interesting problem -- the resource was too big to fit in a single segment, but with the type of code resource that EXCEL required, the compiler didn't let you do segmenting from the project screen (be careful with the project screen, I've found that changing segement names, etc. can link all the way back to some pretty bizzare changes, like the type of resource that gets built). I was finally able to segment the code using pragmas which worked great. You do need to make sure that the main routine that


    gets called is in the "main" segment. I had the best luck with a small main segment that contained the main routine and all the "linkage" code and several other segments that contained the rest of the program. The link map that code warrior produces is useful.

    6) The debugger is great! With one exception: too much single stepping crashes the mac. I found I could set many breakpoints and move around the code, but that if I single-stepped, the program would eventually crash.

    Power pc (using emulator) specific tips and tricks C stuff:

    Carefully go throught the MS EXCEL Readme. It discusses some, but not all, of the limitations of calling code resources from EXCEL on the power PC. Specifically:

    1) your routine can only return a long integer. Any other data type is not supported and gives "invalid procedure call".

    2) There appears to be an undocumented limit of about 12 on the number of parameters you can pass from VBA to the resource (again you get error "invalid procedure call"). I got around problem by making two structures which I passed by reference (it actually made for cleaner code). These structures seem to work fine and in fact allow passing a large amount of data between EXCEL and the code resource.

    3) You have less control over data types with the power PC than you do with the 68K code resources. Their sizes are fixed. So, to align things between PC C++, 68K MAC, powerPC and VBA (which is what I had to do ... ), my approach was: return a long integer (dictated by the power PC). Make doubles 4 bytes, use longs throughout if you are planning to port to native power pc, at least with any integer variable that will pass back and forth to VBA.

    4) Other than #2, the only other change that I had to make to get the code to run was to make struct alignments powerPC on the power PC processor panel in code warrior. If the alignments are different (at least for this project), the code was called but my newly defined data types didn't work.

    VBA stuff:

    The VBA code ported easily with two exceptions:

    1) I could not get the MAC to find the code resource in the system folder. The EXCEL documentation says it should look in current folder, then system folder and then preferences. It didn't for me ..... I wrote some code to set the path so that EXCEL is pointing to the correct directory which solved this problem.

    2) Some icons that I drew in powerpoint didn't port to the MAC. Strangely, if the icons were made from lines, they didn't resize correctly but boxes did. So ... I used boxes instead of lines and all was well.

    Native power pc tips and tricks

    1) It is crystal clear that VBA is looking for a cfrg "resource" (microsoft's terminology). And, in fact unless the name of your code appears in this cfrg resource, you'll get an error "code resource not found" from VBA. In code warrior terms, the only way I could do this was to build a code resource type project and make the resource type cfrg. This made VBA happy as far as the names were concerned. There may well be a way you can do this using shared libraries, unfortunately in my hands the suggested methods in the Code Warrior Documentation did not work with VBA, although they did for other applications (I had a small monitor app running to isolate VBA problems from other problems ... ). It may be possible to get around this problem using Rez. The bottom line is that although there is a place in code warrior where you can name your fragment, VBA doesn't recognize the name. You reference your routine using an alias in vb: alias "cfrg$subname"

    2) The mixed mode manager on the powerpc requires a variable ProcInfoType to be set correctly for the calling convention, returned result (which must be long) and parameters passed on the stack. In fact, this is why there is a limit on the number of parameters you can pass. The largest integer you can represent is . 4294967295, so with many more parameters, you overflow. The calling convention for VBA is pascal, stack based (also not documented anywhere). Calculating the number is kind of painful. On the code


    warrior CD there is a file on _procinfo that contains some code that you can modify to write a small program that does the calculation for you. After that is done, you add a line to your program:

    ProcInfoType _procinfo = 1073741808 or whatever your number is.

    3) This last problem is completely undocumented as far as I can tell and is treacherous. I used a number of structures with both integer and double precision members. They worked fine on the quadra and on the powerpc running the emulator. They didn't work on the powerpc UNTIL I changed all of the integer members to long (actually, it is the VBA end that causes the problem, int and long are both 32 bit for c/c++). Once these were made long, everything worked. As a general rule, if you are passing integer data, set it up as long on the VBA side. The mixed mode manager has a fairly limited set of acceptable codes to use for parameters and perhaps that is why it didn't work. I think (but have not tried .... ) that you can pass 2 byte pammeters but perhaps the problem is if you mix 2 and 4 byte data types in a structure that gets passed between VBA and your code that it causes problems.


    Mac MVP

    Friday, July 13, 2012 8:03 PM
  • The problem is that 'old' Excel was doing a direct memory access to transfer arrays to the dll or Mac equivalent when a CALL was made to it, but under VBA a new system is in place. What is the protocol under VBA on Mac Excel 2011? I would love to find out.
    Monday, May 5, 2014 8:09 PM