2

I'm trying to pass an array I've built from Access VBA to a .net function.

I don't have a com problem as I can reference the library from VBA and I can call a simple function.

My problem exist when I attempt to pass an array to the vb.net function.

vb.net code

            <ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
            Public Class ComClass1
                Public otag As String()
            #Region "COM GUIDs"
                ' These  GUIDs provide the COM identity for this class 
                ' and its COM interfaces. If you change them, existing 
                ' clients will no longer be able to access the class.
                Public Const ClassId As String = "a23f3cee-72c2-4181-9a0a-a8356a42dfcd"
                Public Const InterfaceId As String = "9708311e-492c-4d29-b902-ef53b8db2ae1"
                Public Const EventsId As String = "f785175f-d829-459b-bcbf-405266c1d5a2"
            #End Region

                ' A creatable COM class must have a Public Sub New() 
                ' with no parameters, otherwise, the class will not be 
                ' registered in the COM registry and cannot be created 
                ' via CreateObject.
                Public Sub New()
                    MyBase.New()
                End Sub

                Function CreateAnAsset() As String


                    Return "whohhhoo" 'otag


                End Function

            End Class

Now for the simple call from VBA

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * from Additions_Uploaded")
'DataFieldCount = rs.GetRows(rs.Fields.Count)
AdditionRows = DCount("*", "Additions_Uploaded")

DataFieldCount = rs.GetRows(AdditionRows)

rs.Close
db.Close

AdditionRows = DCount("*", "Additions_Uploaded")
numColumns = UBound(DataFieldCount, 2) + 1
ReDim COLUMN0(0 To numColumns - 1) 'As String
ReDim COLUMN1(0 To numColumns - 1)
ReDim COLUMN2(0 To numColumns - 1)
ReDim COLUMN3(0 To numColumns - 1)
ReDim COLUMN4(0 To numColumns - 1)
ReDim COLUMN5(0 To numColumns - 1)
ReDim COLUMN6(0 To numColumns - 1)
ReDim COLUMN7(0 To numColumns - 1)
ReDim COLUMN8(0 To numColumns - 1)
ReDim COLUMN9(0 To numColumns - 1)
ReDim COLUMN10(0 To numColumns - 1)
ReDim COLUMN11(0 To numColumns - 1)
ReDim COLUMN12(0 To numColumns - 1)
ReDim COLUMN13(0 To numColumns - 1)
ReDim COLUMN14(0 To numColumns - 1)
ReDim COLUMN15(0 To numColumns - 1)
ReDim COLUMN16(0 To numColumns - 1)
ReDim COLUMN17(0 To numColumns - 1)
ReDim COLUMN18(0 To numColumns - 1)
ReDim COLUMN19(0 To numColumns - 1)
ReDim COLUMN20(0 To numColumns - 1)
ReDim COLUMN21(0 To numColumns - 1)
ReDim COLUMN22(0 To numColumns - 1)
ReDim COLUMN23(0 To numColumns - 1)
ReDim COLUMN24(0 To numColumns - 1)
ReDim COLUMN25(0 To numColumns - 1)
' ReDim Inv(0 To numRecords - 1) As ujk
MsgBox "There are " & numColumns & " fields in the upload table."
MsgBox AdditionRows & " Is the number of records in the upload table."

Close #1
'Open "C:\Transient\BannerText.txt" For Output As #1

For i = 0 To numColumns - 1
    COLUMN0(i) = DataFieldCount(0, i)
    COLUMN1(i) = DataFieldCount(1, i)
    COLUMN2(i) = DataFieldCount(2, i)
    COLUMN3(i) = DataFieldCount(3, i)
    COLUMN4(i) = DataFieldCount(4, i)
    COLUMN5(i) = DataFieldCount(5, i)
    COLUMN6(i) = DataFieldCount(6, i)
    COLUMN7(i) = DataFieldCount(7, i)
    COLUMN8(i) = DataFieldCount(8, i)
    COLUMN9(i) = DataFieldCount(9, i)
    COLUMN10(i) = DataFieldCount(10, i)
    COLUMN11(i) = DataFieldCount(11, i)
    COLUMN12(i) = DataFieldCount(12, i)
    COLUMN13(i) = DataFieldCount(13, i)
    COLUMN14(i) = DataFieldCount(14, i)
    COLUMN15(i) = DataFieldCount(15, i)
    COLUMN16(i) = DataFieldCount(16, i)
    COLUMN17(i) = DataFieldCount(17, i)
    COLUMN18(i) = DataFieldCount(18, i)
    COLUMN19(i) = DataFieldCount(19, i)
    COLUMN20(i) = DataFieldCount(20, i)
    COLUMN21(i) = DataFieldCount(21, i)
    COLUMN22(i) = DataFieldCount(22, i)
    COLUMN23(i) = DataFieldCount(23, i)
    COLUMN24(i) = DataFieldCount(24, i)
    COLUMN25(i) = DataFieldCount(25, i)
    ' Inv(i).Nr = data(1, i)

    Dim RecordNumber As Integer
    Dim TextRecord As String

    TextRecord = DataFieldCount(0, i) & " " & DataFieldCount(1, i) & "  " & DataFieldCount(2, i) & "  " & DataFieldCount(3, i) & "  " & DataFieldCount(4, i) & "  " & _
      DataFieldCount(5, i) & " " & DataFieldCount(6, i) & "  " & DataFieldCount(7, i) & "  " & DataFieldCount(8, i) & "  " & DataFieldCount(9, i) & "  " & _
      DataFieldCount(10, i) & " " & DataFieldCount(11, i) & "  " & DataFieldCount(12, i) & "  " & DataFieldCount(13, i) & "  " & DataFieldCount(14, i) & "  " & _
      DataFieldCount(15, i) & " " & DataFieldCount(16, i) & "  " & DataFieldCount(17, i) & "  " & DataFieldCount(18, i) & "  " & DataFieldCount(19, i) & "  " & _
      DataFieldCount(20, i) & " " & DataFieldCount(21, i) & "  " & DataFieldCount(22, i) & "  " & DataFieldCount(23, i) & "  " & DataFieldCount(24, i) & "  " & _
      DataFieldCount(25, i)
    'Print #1, TextRecord
Next i

' serv.
serv.otag (COLUMN25)

My last line attempts to pass the string array which is typed variant in VBA to the public variable of otag but I get an error that says.

object doesn't support this property or method

I reiterate, I can do a simple string. It appears any attempt to send an array gives me an error on the VBA code.

1 Answer 1

1

Unless I'm missing something, it looks like you are calling the otag property as if it is a method rather than setting it to the new value (e.g. serv.otag = COLUMN25)

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.