【VBA】C++を利用して処理の高速化

// addition.cpp

#include <iostream>

extern "C" __declspec(dllexport) double Add(double a, double b) {
    return a + b;
}

g++ -shared -o addition.dll addition.cpp

Declare Function Add Lib "path\to\your\dll\addition.dll" (ByVal a As Double, ByVal b As Double) As Double

Sub TestAddition()
    Dim result As Double
    result = Add(5, 10)
    MsgBox "Result: " & result
End Sub

// ArrayProcessor.cpp
#include <vector>

extern "C" __declspec(dllexport) void Process2DArray(int rows, int cols, int** array) {
    // Processing the 2D array
    for (int i = 0; i < rows; ++i) {
        for (int j = 0; j < cols; ++j) {
            array[i][j] = array[i][j] * 2;  // Example: Multiply each element by 2
        }
    }
}

Declare Sub Process2DArray Lib "path\to\ArrayProcessor.dll" (ByVal rows As Long, ByVal cols As Long, ByRef array() As Long)

Sub ExampleUsage()
    Dim rows As Long
    Dim cols As Long
    Dim myArray() As Long
    Dim i As Long, j As Long

    ' Set the dimensions of the 2D array
    rows = 3
    cols = 4

    ' Redim the VBA array to hold the values
    ReDim myArray(1 To rows, 1 To cols) As Long

    ' Fill the VBA array with some initial values
    For i = 1 To rows
        For j = 1 To cols
            myArray(i, j) = i * j
        Next j
    Next i

    ' Call C++ function to process the array
    Call Process2DArray(rows, cols, myArray)

    ' Display the processed array
    Debug.Print "Processed Array:"
    For i = 1 To rows
        For j = 1 To cols
            Debug.Print myArray(i, j);
        Next j
        Debug.Print ""
    Next i
End Sub

// DatabaseProcessor.cpp
#include <sqlite3.h>
#include <string>

extern "C" __declspec(dllexport) int ExecuteQuery(const char* query, char* errorMessage, const char* dbName) {
    sqlite3* db;
    char* errMsg = nullptr;

    int result = sqlite3_open(dbName, &db);

    if (result != SQLITE_OK) {
        strcpy(errorMessage, sqlite3_errmsg(db));
        sqlite3_close(db);
        return result;
    }

    result = sqlite3_exec(db, query, nullptr, nullptr, &errMsg);
    if (result != SQLITE_OK) {
        strcpy(errorMessage, errMsg);
    }

    sqlite3_close(db);
    return result;
}

extern "C" __declspec(dllexport) int InsertData(const char* tableName, const char* values, char* errorMessage, const char* dbName) {
    std::string query = "INSERT INTO " + std::string(tableName) + " VALUES (" + std::string(values) + ");";
    return ExecuteQuery(query.c_str(), errorMessage, dbName);
}

extern "C" __declspec(dllexport) int UpdateData(const char* tableName, const char* setValues, const char* condition, char* errorMessage, const char* dbName) {
    std::string query = "UPDATE " + std::string(tableName) + " SET " + std::string(setValues) + " WHERE " + std::string(condition) + ";";
    return ExecuteQuery(query.c_str(), errorMessage, dbName);
}

extern "C" __declspec(dllexport) int DeleteData(const char* tableName, const char* condition, char* errorMessage, const char* dbName) {
    std::string query = "DELETE FROM " + std::string(tableName) + " WHERE " + std::string(condition) + ";";
    return ExecuteQuery(query.c_str(), errorMessage, dbName);
}

extern "C" __declspec(dllexport) int SelectData(const char* tableName, const char* columns, const char* condition, char* result, char* errorMessage, const char* dbName) {
    std::string query = "SELECT " + std::string(columns) + " FROM " + std::string(tableName) + " WHERE " + std::string(condition) + ";";
    
    sqlite3* db;
    int resultCount = 0;
    char* errMsg = nullptr;

    int resultDb = sqlite3_open(dbName, &db);

    if (resultDb != SQLITE_OK) {
        strcpy(errorMessage, sqlite3_errmsg(db));
        sqlite3_close(db);
        return resultDb;
    }

    sqlite3_stmt* stmt;
    int resultPrepare = sqlite3_prepare_v2(db, query.c_str(), -1, &stmt, nullptr);

    if (resultPrepare != SQLITE_OK) {
        strcpy(errorMessage, sqlite3_errmsg(db));
        sqlite3_close(db);
        return resultPrepare;
    }

    int cols = sqlite3_column_count(stmt);

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        for (int col = 0; col < cols; col++) {
            strcat(result, reinterpret_cast<const char*>(sqlite3_column_text(stmt, col)));
            strcat(result, ",");
        }
        strcat(result, "\n");
        resultCount++;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return resultCount;
}

Declare Function ExecuteQuery Lib "path\to\DatabaseProcessor.dll" (ByVal query As String, ByVal errorMessage As String, ByVal dbName As String) As Long
Declare Function InsertData Lib "path\to\DatabaseProcessor.dll" (ByVal tableName As String, ByVal values As String, ByVal errorMessage As String, ByVal dbName As String) As Long
Declare Function UpdateData Lib "path\to\DatabaseProcessor.dll" (ByVal tableName As String, ByVal setValues As String, ByVal condition As String, ByVal errorMessage As String, ByVal dbName As String) As Long
Declare Function DeleteData Lib "path\to\DatabaseProcessor.dll" (ByVal tableName As String, ByVal condition As String, ByVal errorMessage As String, ByVal dbName As String) As Long
Declare Function SelectData Lib "path\to\DatabaseProcessor.dll" (ByVal tableName As String, ByVal columns As String, ByVal condition As String, ByVal result As String, ByVal errorMessage As String, ByVal dbName As String) As Long

Sub DatabaseCRUDOperations()
    Dim result As Long
    Dim errorMessage As String
    Dim query As String
    Dim values As String
    Dim setValues As String
    Dim condition As String
    Dim tableName As String
    Dim columns As String
    Dim resultData As String

    ' SQLiteデータベースファイルのパス
    Dim dbName As String
    dbName = "C:\path\to\your\database.db"

    ' CREATE (INSERT)
    tableName = "example_table"
    values = "'Alice', 25, 'USA'"  ' Example values
    result = InsertData(tableName, values, errorMessage, dbName)

    ' READ (SELECT)
    tableName = "example_table"
    columns = "name, age, country"
    condition = "age > 20"
    result = SelectData(tableName, columns, condition, resultData, errorMessage, dbName)
    Debug.Print "Select Result: " & resultData

    ' UPDATE
    tableName = "example_table"
    setValues = "age = 30"
    condition = "name = 'Alice'"
    result = UpdateData(tableName, setValues, condition, errorMessage, dbName)

    ' DELETE
    tableName = "example_table"
    condition = "name = 'Alice'"
    result = DeleteData(tableName, condition, errorMessage, dbName)
End Sub