VB.NETからVBAマクロ(引数)を呼び出す方法

質問

VB.NETから参照型引数付きのFunctionのVBAマクロを呼び出す方法

Excel2003のVBEの標準モジュールModule1にあるFunctionのマクロtest(引数)をVB.NETから呼び出して、引数に設定した値をVBA側で加工して、加工された値をVB.NETで取り出したいのですが、VB.NET側でどのように記述してよいのかがよく分かりません。

具体的には、以下のVBAをVB.NETから呼び出すにはどのように記述すればよいのでしょうか。できましたらそのままVB.NETのConsoleApplicationのModule1のSub Main()の中にコピーペーストして動くコードと参照設定を教えていただけると助かります。

---Excel2003のVBA(C:\test\Book1.xls)----------
Function test(ByRef data As Long) As Long
　If data = 0 Then
　　test = 0
　　data = 0
　Else
　　test = 1
　　data = 100 / data
　End If
End Function
----------------------------------------------

よろしくお願いします(WindowsXP,VisualStudio2010,Excel2003VBE)

回答No.6

angel_Z

こんにちは。

＞参照型の引数として呼び出すことは原理的にできないのでしょうか。
そうですね。。。
できないと思われます。
戻り値は取れるんですけどね。

ただ値を取得するのであれば、すでに解答したので対応出ると思いますが、
細かい事は、実際のソースを見て判断しないと何とも言えません。

いろいろ考えてみましたが、Excelのバージョンを考えると、
作り変えた方が早いのではないでしょうか。
私はそういう場合、クラスに作り変えたりします。

時間が経てば、そのうちひらめく場合もありますけど(^o^)

今はお役に立てなくてごめんなさい。
逆にひらめいたら、教えて下さい(*・∇-)

お礼

こんにちは。

参照型の引数として呼び出すことができないということが分かっただけでも質問してよかったです。
そのほかにもいろいろな回避策を教えていただきまして大変参考になりました。

ありがとうございました。

回答No.5

angel_Z

こんにちは。

こんなのはどうでしょう？
クリップボードに値をコピーさせてみては。
タイプライブラリは無かった事に。。。

Microsoft Forms 2.0 Object Library
VBAと.NETの参照設定に追加します。
VBAの参照設定に無い場合は、FM20.DLLを参照から追加して下さい。
参考URLを付けておきます。

------VBA------------------------------------
Function test(ByRef data As Long) As Long
Dim CB As DataObject
Set CB = New DataObject

If data = 0 Then
test = 0
data = 0
Else
test = 1
data = 100 / data
End If

'クリップボードのクリア
Application.CutCopyMode = False

'クリップボードに値をセット
CB.SetText data
'コピーの実行（これを実行しないとクリップボードにコピーされません。）
CB.PutInClipboard
End Function

---------.NET------------------
Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

Sub Main()

Try
Dim oExcel As New Excel.Application
Dim oBook As Excel.Workbook
Dim oBooks As Excel.Workbooks = oExcel.Workbooks
Dim strPath As String
Dim Result As Long

Dim data As Long
'C:\test\Book1.xlsを実行ファイルに指定する。
strPath = "C:\test\Book1.xls"

'Excelオブジェクトの設定
oExcel.Visible = False
oBook = oBooks.Open(strPath)

data = 1

'Excel側のFunctionを実行する
Result = oExcel.Run("'" & oBook.Name & "'!test", data)

'クリップボードに保存された値を取得
If Clipboard.ContainsText() Then
MsgBox(Clipboard.GetText())
End If

'解放
oBook.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing

System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks = Nothing

oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

End Module

プログラムの実行でクリップボードにコピーの動作がないようでしたら、
こちらで対応できそうなのですが…

参考URL：

http://officetanaka.net/excel/vba/tips/tips20.htm

補足

angel_Zさん

こんにちは。

いろいろ提案をしていただきましてありがとうございます。

教えていただきました戻り値経由による方法、および今回のクリップボード経由による方法を確認しました。呼び出し先で変更された結果を呼び出し元で得ることができたのですが1つ問題があります。

すでにあるVABのマクロをVB.NETからそのまま呼び出して利用したいのですが、これらのマクロの引数は、サンプル例のように整数型の変数が1個だけではなく、複数の引数を持っています。型も整数型だけではなく他の型も混在していたり、ユーザー定義型もあります。クリップボードでこれらの引数の値のすべてを返すことは可能でしょうか。できたとしても処理が複雑になるような気がしているのですが。。。

マクロ自体も複数あり、各マクロ内部の処理も大掛かりな処理をしていてVBへの移植が大変なので、少しの修正程度で、できれば何も手を加えずそのまま呼び出したかったのですが、参照型の引数として呼び出すことは原理的にできないのでしょうか。

よろしくお願いします。

回答No.4

angel_Z

こんにちは。

相談ですが、
エクセル側のFunction testを修正する事が可能ですか？
可能でしたら、

Function test(ByRef data As Long, Optional fg As Integer = 0) As Long
'fg 0:VBAより 1:.NETより
　　If data = 0 Then
　　　　test = 0
　　　　data = 0
　　Else
　　　　If fg = 0 Then
　　　　　　test = 1
　　　　　　data = 100 / data
　　　　Else
　　　　　　test = 100 / data
　　　　End If
　　End If
End Function

に修正して、.NET側のマクロの呼び出しで、
　　'Excel側のFunctionを実行する
　　Result = oExcel.Run("'" & oBook.Name & "'!test", data,1)
にすると、100が帰ってきます。

VBA側は、fgはデフォルト0で省略可能なので、
変更しなくてOKなのですが。
こちらが一番簡単な変更なのかと考え方を変えてみたのですが、
いかがでしょうか？

変更できない場合は、
自作のDLL作成で両方の参照設定や、
API使用などありそうですが、
ややこしいです。。。

補足

angel_Zさん

返答ありがとうございます。

>エクセル側のFunction testを修正する事が可能ですか？

少しの修正は可能ですが、引数には反映されず、結果が戻り値で返るのは、ちょっと問題があります。引数として返されるのであれば上記のコードの程度の修正はOKです。

>変更できない場合は、
>自作のDLL作成で両方の参照設定や、
>API使用などありそうですが、
>ややこしいです。。。

かなりややこしそうで苦戦しております。ANo.3 で教えていただきましたリンク先のサンプルで確認していますがもう少し時間がかかりそうです。結果は ANo.3 のところにご報告致します。

よろしくお願いします。

回答No.3

angel_Z

こんにちは。

参照渡しでしたね。

この辺りが参考になりそうですが、
http://hpcgi1.nifty.com/MADIA/vbnet/wwwlng.cgi?print+201210/12100013.txt

タイプライブラリを用意して、
両方に参照設定の必要がありますね。

補足

angel_Zさん

返答ありがとうございます。

分からないことだらけでいろいろ確認していて返答が遅くなってしまいました。

> この辺りが参考になりそうですが、
> http://hpcgi1.nifty.com/MADIA/vbnet/wwwlng.cgi?print+201210/12100013.txt
> タイプライブラリを用意して、
> 両方に参照設定の必要がありますね。
> 変更できない場合は、
> 自作のDLL作成で両方の参照設定や、
> API使用などありそうですが、
> ややこしいです。。。

ややこしくても、このようにすることで問題が解決するのであれば、やってみる価値はあると思いましたので、リンク先のサンプルを参考にして確認してみました。どこかが間違っているのかもしれませんが、下記のMsgBox(t_o.data)の結果は100にはならず1のままでした。

---Excel2003のVBA(C:\test\Book1.xls)----------

Option Explicit

Function test(ByRef t_o As ClassLibrary1.T) As Long

If t_o.data = 0 Then
test = 0
t_o.data = 0
Else
test = 1
t_o.data = 100 / t_o.data
End If

End Function

---VB.NET(ConsoleApplication)-------------

Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

Sub Main()

Try
Dim oExcel As New Excel.Application
Dim oBook As Excel.Workbook
Dim oBooks As Excel.Workbooks = oExcel.Workbooks
Dim strPath As String
Dim Result As Long

'Dim data As Long
Dim t_o As New ClassLibrary1.T ' <--変更

'C:\test\Book1.xlsを実行ファイルに指定する。
strPath = "C:\test\Book1.xls"

'Excelオブジェクトの設定
oExcel.Visible = False
oBook = oBooks.Open(strPath)

'data = 1
t_o.data = 1 ' <--変更

'Excel側のFunctionを実行する
'Result = oExcel.Run("'" & oBook.Name & "'!test", data)
Result = oExcel.Run("'" & oBook.Name & "'!test", t_o) ' <--変更

'確認用
MsgBox(Result)
'MsgBox(data)
MsgBox(t_o.data) ' <--変更

'解放
oBook.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing

System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks = Nothing

oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

End Module

---VB.NET(ClassLibrary1)-----------------------

Public Structure T

'Public data As Long 'Longではエラーに?
Public data As Integer

End Structure

------------------------------------------------

もし、angel_Zさんの意図されている方法と異なっていましたらご指摘ください。

よろしくお願いします。

回答No.2

angel_Z

こんにちは。

これではどうですか？

'参照設定の追加 COM
'Microsoft Excel xx.0 Object Libraryを追加

Imports Excel = Microsoft.Office.Interop.Excel

Sub Main()

　Try
　　Dim oExcel As New Excel.Application
　　Dim oBook As Excel.Workbook
　　Dim oBooks As Excel.Workbooks = oExcel.Workbooks
　　Dim strPath As String
　　Dim Result As Long

　　'C:\test\Book1.xlsを実行ファイルに指定する。
　　strPath = "C:\test\Book1.xls"

　　'Excelオブジェクトの設定
　　oExcel.Visible = False
　　oBook = oBooks.Open(strPath)

　　'Excel側のFunctionを実行する
　　Result = oExcel.Run("'" & oBook.Name & "'!test", 引数)

　　'確認用
　　Msgbox(Result)

　　'解放
　　oBook.Close(False)
　　System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
　　oBook = Nothing

　　System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
　　oBooks = Nothing

　　oExcel.Quit()
　　System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
　　oExcel = Nothing

　Catch ex As Exception
　　MsgBox(ex.Message)
　End Try
End Sub

補足

angel_Zさん。こんにちは。

返答ありがとうございます。

教えていただきましたコードで確認してみました。
エラーは出ず実行して正常に終了するのですが
VBA側で変更した引数の値が
VB.NETの引数には反映されてこないようです。

--------------------
(省略)

Dim data As Long '<-----追加
data = 1 '<-----追加

'Excel側のFunctionを実行する
Result = oExcel.Run("'" & oBook.Name & "'!test", data) ' <-----修正

'確認用
MsgBox(Result)
MsgBox(data) '<-----追加

(省略)
--------------------

MsgBox(data)のdataの期待値は100ですが実際は1のままです。

よろしくお願いします。

回答No.1

yamaj_biz

「http://dobon.net/vb/dotnet/programing/eval.html」の「CSharpCodeProviderを使用した方法」が参考になるのではないでしょうか。

参考URL：

http://dobon.net/vb/dotnet/programing/eval.html

補足

yamaj_bizさん

返答ありがとうございます。

すでにあるVABのマクロの処理をVBでも処理したいのですが、そのマクロはサンプル例のように単純ではなく、かなり大掛かりな処理をしています。それでVBへの移植が大変なので、そのままVABのマクロを呼び出せないのかを検討しています。

よろしくお願いします。