1

The question is how to return a string from java method in VBA. For example I have a code in java:

package someclass;

public class SomeClass {

    public static String someMethod(String s){
        return "this is some string: "+s;
    }

    public static void main(String[] args) {
        System.out.println(someMethod("Hello world!"));
    } 

}

Now the question is how to call someMethod in VBA?

Community
  • 1
  • 1
eugene
  • 71
  • 1
  • 8
  • Not sure what you're asking, do you want to convert this java code to VBA? – crimson589 Dec 18 '17 at 23:21
  • The only way I see is you make a DLL (using C++, VB.NET or similar others) that consumes your Java functions, then add a reference to that DLL to your VBA project and call them via the DLL which serves as bridge. As far as I know, there's no direct bridge between VBA and Java. You might run a method from VBA of your JAR file using a shell command, but I don't think you can return the function in VBA though. – Matteo NNZ Dec 18 '17 at 23:28
  • This is just example. Off course there is no sense to use particular example in real VBA. I had encryptor and decryptor methods in java, so I want to use this methods in VBA. – eugene Dec 18 '17 at 23:33
  • https://stackoverflow.com/questions/11343769/microsoft-excel-macro-to-run-java-program – Slai Dec 19 '17 at 00:56
  • I tried to use ikmvc (http://weblog.ikvm.net/2015/08/26/IKVMNET81ReleaseCandidate0.aspx) to convert jar to dll, but when I registered it with regsvr32 I got the error message: The module "\mydll.dll" was loaded but the entry-point DllRegisterServer was not found. – eugene Dec 19 '17 at 11:37
  • Thanks to @Slai, the way proposed by mschwehl in link stackoverflow.com/questions/11343769/ does work. I just need to do in java: System.out.print() and so I can get it in VBA. – eugene Dec 19 '17 at 12:22
  • So using \n Set oShell = CreateObject("WScript.Shell") 'run command' Dim oExec As Object Dim oOutput As Object Set oExec = oShell.exec(sCmd) Set oOutput = oExec.StdOut – eugene Dec 19 '17 at 12:22
  • Oh. Sorry. Thanks for advice! – eugene Dec 19 '17 at 12:30

2 Answers2

1

Use Jinx, the Excel Java Add-In, to call your Java method as an Excel macro.

See https://exceljava.com for details.

All you would need to do in your example is to add the @ExcelMacro annotation to your method to make it callable from Excel as a macro.

package someclass;

import com.exceljava.jinx.ExcelMacro;

public class SomeClass {

    @ExcelMacro
    public static String someMethod(String s){
        return "this is some string: "+s;
    }

    public static void main(String[] args) {
        System.out.println(someMethod("Hello world!"));
    } 

}

See https://exceljava.com/docs/macros.html for more details about writing Excel macros in Java using Jinx.

Tony Roberts
  • 387
  • 1
  • 5
0

Thanks to @mschwehl answer (i.e. possible solution) here:
stackoverflow.com/questions/11343769/

eugene
  • 71
  • 1
  • 8