How to Call VBA function from VC++

[Mehul Khatri]

Hi

How to call the VBA function from the VC++?


Thanks
Mehul Khatri

 

[hrlngrv]

One way, arguably crude, would be to connect to the running Excel instance from the VC++ program using VC++'s equivalent to VBA's GetObject, then use the Excel application class's Evaluate or Run methods to call the VBA function.

 

[Derek Brown]

Hello and welcome to The Board.
Not being familiar with Visual C++, I don't know if this will help but here is an example of the code that I use with Visual Basic.NET

Imports System.IO
Module Module1

    Public Sub Main()

        RunMyWorkbook()

    End Sub

    Sub RunMyWorkbook()
        Const strControlFileName As String = "P:\MyFolder\MyWorkbook.xlsm"
        Dim oExcel As Microsoft.Office.Interop.Excel.Application
        Dim oBook As Microsoft.Office.Interop.Excel.Workbook
        Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks
        Dim strFilenameCheck As String
        Dim strMacroName As String
        Try
            strMacroName = "RunMyWorkbook"
            oExcel = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
            oExcel.Visible = False
            oBooks = CType(oExcel.Workbooks(), Microsoft.Office.Interop.Excel.Workbooks)
            strFilenameCheck = Dir(strControlFileName)
            If strFilenameCheck <> "" Then
                oBook = CType(oBooks.Open(strControlFileName), Microsoft.Office.Interop.Excel.Workbook)
                oExcel.DisplayAlerts = False
                oExcel.Run(strMacroName)
                oExcel.DisplayAlerts = True
            Else
                Dim sw As New StreamWriter(Application.StartupPath & "\MyWorkbook_error.log", True)
                sw.WriteLine(Now() & " - '" & strControlFileName & "' could not be accessed.")
                sw.Close()
                End
            End If
            '
        Catch ex As Exception
            Dim sw As New StreamWriter(Application.StartupPath & "\MyWorkbook_Error.log", True)
            sw.WriteLine(Now() & " - " & ex.Message)
            sw.Close()
        Finally
            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
            GC.Collect()
        End Try
    End Sub
End Module

The .exe file, when run, calls the Excel macro which in my case is Excel 2007 to perform various actions.
However, you mentioned calling a 'function' - these would normally return a value but you did not say whether you are trying to return values to the VC++ process. If that is the case, you may be able to adapt the code to do what you want.