JavaScript to call a VBA routine with parameters

Question

I need to pass arguments to a Excel VBA code from JavaScript of HTA.
1. I can successfully call VBA function, but unable to pass string arguments correctly.
2. JavaScript function can pass different string arguments.
3. Below is code in simple and demo form.
4. Excel-VBA code
```
Sub subTest(strName As String)
MsgBox strName
End Sub
```

HTA code with Javascript

<!DOCTYPE html>
<html>
<head>
<title>HTA</title>
<hta:application 
id="oHta"
applicationname="htaNavi"
border="1"
borderstyle = normal
contextmenu = "yes"
caption="Navigator"
sysmenu="yes"
WINDOWSTATE="maximize"
>
</head>
<body>
<input type="button" value="testing" onclick="funRun('testng string')" />
<input type="button" value="testing second" onclick="funRun('testng')" />
</body>

<script>

var objExl;
var objWb;
var objExl =new ActiveXObject("Excel.Application");
objExl.Visible = true;
var objWb = objExl.Workbooks;
var strpath = '\path\testing_excel_web.xls';
objWb.Open(strpath);

function funRun(strName)
{
alert(strName);
objWb.Application.Run('testing_excel_web.xls!subTest(strName)');
}
</script>
</html>

I can call subTest, but message box populates strName as string but not 'testing string' as text.

Ian · Accepted Answer · 2013-01-24 23:33:34Z

1

I'm thinking you want:

objWb.Application.Run('testing_excel_web.xls!subTest("' + strName + '")');

This way, the value of the variable strName is concatenated to the command you are attempting to run.

I know nothing about this calling of a VBA function, so I'm not sure if you need the " around the strName like I provided or not.

In addition, to be safe, in case your strName value contains ", you should use this:

objWb.Application.Run('testing_excel_web.xls!subTest("' + strName.replace(/"/g, "\"") + '")');

Hopefully with this, the value of strName could be

The word "testing" here
or
"Here's a quote"

and it will still work.

The point is that if the string contains ", the Javascript would/could fail. If it would absolutely never contain ", then forget about it. But I think it's needed since any " in the value of strName will break the passing of it as a parameter.

edited Jan 24, 2013 at 23:33

answered Jan 24, 2013 at 21:30

Ian

51k13 gold badges104 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

msinfo Over a year ago

Thanks for consideration. I tried that and got error : can't run the macro 'testing_excel_web.xls!subTest(testing string)'.

msinfo Over a year ago

Thank you very much! You saved me from writing 100 lines of repetative code lines. I used to thought programming is all about using keywords, but . . . You guessed right problem was indeed with ""

Ian Over a year ago

Awesome, glad it helped! I had a feeling the quotes were needed. Anyways, I edited my answer to include a "safer" solution, in case the string contains a " itself. With my first solution, try passing your function the value "asdf", and see if it still works...I don't think it will. If it doesn't, use my second solution. If you have any trouble/questions, let me know.

msinfo Over a year ago

True. Just passing "asdf" as argument fails (Error : Unterminated string). But if used with your second method, it works. Thanks for proper explanation.

Collectives™ on Stack Overflow

JavaScript to call a VBA routine with parameters

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related