Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using JAVA inside VBA (EXCEL 2003 VBA)

    I am wondering if there is any way to use JAVA code inside a VBA module (directly or indirectly) ?

    I know **zero** about JAVA but I have a "kind of CSV" type data file that uses an odd date format created via JAVA.
    Basically I want to parse the data both ways so that the datafile can be read from / written to by my spreadsheet.
    The JAVA sourcecode is under GNU on sourceforge so I can post a link to it if that would help.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Using JAVA inside VBA (EXCEL 2003 VBA)

    VBA is an interpreted language, and as far as I know, the interpreter only speaks the one language. If you can compile the Java code into a DLL, you could call its functions/methods from VBA. Not sure that's any easier/faster than converting the code...

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using JAVA inside VBA (EXCEL 2003 VBA)

    I thought about Jsher's comments, did some more searching, and found this:

    http://sparcs.kaist.ac.kr/~paladin/study/u...0.HTM#Heading12

    <hr>
    Calling Java Objects from Excel

    Microsoft Excel and other Microsoft Office products have their own version of Visual Basic built-in. This means, of course, that you can also access Java objects from Excel!

    CAUTION
    You must use a 32-bit version of Excel in order for this to work. The 16-bit versions do not use 32-bit COM access, and cannot access Java objects. This example is shown with Excel 7.0a from the Office for Windows 95 suite.

    To create an Excel function, start Excel and choose Insert, Macro, Module from the main menu, as shown in Figure 50.6.

    FIG. 50.6
    To create an Excel function, you need to insert a code module.

    The function must access the JavaObject class, and then call reverseString. Listing 50.3 shows the Reverser$ function.
    Listing 50.3Reverser$ Function from ExcelDemo.xls

    <pre>Function Reverser$(reverseMe$)
    Dim javaob As Object
    Set javaob = CreateObject("JavaObject")
    Reverser$ = javaob.reverseString(reverseMe$)
    End Function
    </pre>


    NOTE: Make sure that you have a recent version of the Microsoft Java SDK. The earliest versions had problems with the COM integration.

    Once you have defined this function, you can use it in your spreadsheet. For example, assume that you want to take the information in cell A1 in the spreadsheet, reverse it, and place the results in cell A2. Just go to cell A2 and type the following formula:

    <pre>=Reverser(A1)
    </pre>


    Notice that there is no $ at the end of Reverser in this case. Now, any text you type in A1 will automatically appear reversed in A2. Figure 50.7 shows an example spreadsheet.

    FIG. 50.7
    Excel can use Java objects to perform interesting functions.<hr>

    Since I really know ***nothing*** about working with java code can someone maybe help me understand this enough to use it for this one-time need? Here is the sourcecode link for the relevant routine:

    http://nosleepsoftware.cvs.sourceforge.net...amp;view=mar kup

    Also, I don't know if this means anything but I have a "jar" file of this code -- is that the compiled format Jsher refers to ?

    Thanks

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Using JAVA inside VBA (EXCEL 2003 VBA)

    Unfortunately, those instructions date back to 1998, when Microsoft was actively competing with Sun over the future of Java and had created its own toolkit. I'm not sure those instructions would work today, or that it is worth the trouble.

    I think we should just look at the data and see whether it would be relatively easier to interpret in VBA. Can you post some examples of the file format? Anonymize/sanitize the data as needed to protect the privacy of the subjects.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using JAVA inside VBA (EXCEL 2003 VBA)

    I strongly suspect you'll be better off creating the entire parsing routine in native VBA, rather than trying to make VBA use java functionality.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •