Excel 2003 VBA calling VB.NET ?

[Guest]

I am having issues after upgrading the VB.NET to use .NET 2 as needed per a
Web Service.
I am having problems with the return variable from the VB.NET to the VBA code:

VBA invoked from Excel:
<code>

Public Sub MSNAddTemplateCampaigns(ByVal AccountID As Long, ByRef
MonthlyBudget As Long)

Dim API As New ADcenter.API

Dim result As Object

Dim Row As Integer

Dim aResults() As Long

Dim Campaigns() As ADcenter.AdCenterCampaign

Dim var As Variant



Dim sheet As Worksheet

Dim intI As Integer

Dim sCampaignName As String



On Error GoTo errhndle

Application.ScreenUpdating = False



Set sheet = Sheets(CampaignsSheetName)

'Build Campaigns object to send to API.AddCampaigns

MSNTemplateCreateCampaignsObject Campaigns, MonthlyBudget

'Define Variant var to be equal to the Campaigns() just created and pass
into AddCampaigns API

var = Campaigns



Set result = API.AddCampaigns(AccountID, var)

aResults = result

sheet.Cells(19, 1) = "Campaign ID"

Row = 21

For i = LBound(aResults) To UBound(aResults)

If aResults(i) <> "0" Then



'Store the newly created campaign ids



If sCampaignName <> var(aResults(i)).CampaignName Then

sCampaignName = var(aResults(i)).CampaignName

MsgBox "Finished Adding Campaign: " & sCampaignName, vbOKOnly,
"MSN AdCenter"

End If

Do While sheet.Cells(Row, 3) <> ""

If LCase(Trim(sheet.Cells(Row, 3))) = LCase(var(i).CampaignName)
Then

sheet.Cells(Row, 1) = aResults(i)

End If

Row = Row + 1

Loop

End If

Next i



MSNHandleAPIErrors result, "addcampaigns", var

Set API = Nothing

Exit Sub

Application.ScreenUpdating = True

errhndle: MsgBox Err.Description, vbOKOnly, Err.Number

Set API = Nothing

End Sub

</code>

Throws an error, 424 Object Required, after completing the Set result =
API.AddCampaigns(AccountID, var) when attempting the assignment aResults =
result...


<.NET code>

Public Function AddCampaigns(ByVal accountID As Integer, ByRef
Campaigns As Object) As Object Implements _main.AddCampaigns

Dim pAPI As CampaignManagement = New CampaignManagement

SetAuthCredentials(pAPI)

Dim i As Integer

Dim lCampaigns() As AdCenterCampaign

ReDim lCampaigns(UBound(Campaigns) - LBound(Campaigns))

Dim aResults() As Long

ReDim aResults(UBound(Campaigns) - LBound(Campaigns))

Dim result As EntityResultType = New EntityResultType

For i = LBound(Campaigns) To UBound(Campaigns)

lCampaigns(i - -LBound(Campaigns)) = Campaigns(i)

aResults(i) = 0

Next i



Try



result = pAPI.AddCampaigns(0, accountID, lCampaigns)

'Handle the Campaigns created successfully

'created.Id is the Campaign ID;

'created.Index is the corresponding index into Campaign

For Each created As EntitySuccessType In result.SuccessRow

aResults(created.Index) = created.Id

Next

AddCampaigns = aResults

Catch e As Exception

WriteToEventLog(accountID & ":" & lCampaigns(0).CampaignId &
lCampaigns(0).CampaignName & ":" & accountID & ":" & UBound(Campaigns) & ":"
& LBound(Campaigns) & ":i=" & i & ":" & e.Message & ":" & e.Source & ":" &
e.StackTrace)

End Try



'AddCampaigns = pAPI.AddCampaigns(0, accountID, lCampaigns)

pAPI = Nothing

End Function

</.NET code>

Guess I'm doing something wrong in the type of variables I'm using.

David

 

[NickHK]

Unless I'm missing something, you're trying to set an unitialised array of
longs equal to an object :

Dim result As Object
Dim aResults() As Long

Set result = API.AddCampaigns(AccountID, var)
aResults = result

NickHK

 

[Guest]

OK, thanks for the feedback.

The previous version of the VBA worked swell with the VB.NET when using .NET
1.1 framework. The API writers mandated a migration to the .NET 2.0
framework and the VBA app no longer could accept the return value from the
function, EntitySuccessType due to:
<quote>
compatibilty issue involving nullable primitives and DotNet Framework 1.1.

Our V3 WSDLs use Nullable primitives (a feature fully supported by DotNet
Framework 2.0 and greater but not 1.1) this explains why developers using the
older DotNet Framework 1.1 experience this issue.

I would suggest that those who use DotNet Framework 1.1 migrate to DotNet
Framework 2.0 (or above) for an immediate workaround (not to mention the
long-term benefits of migrating to the newer framework).

NOTE: Current users of DotNet Framework 2.0 or above and Java/Perl/PHP/etc.
users are NOT affected by this issue, please continue migrating to our V3
WSDLs if you have not already done so.
</quote>

So before the migration the code:
<prev>
Dim result = Object
Set result = API.AddCampaigns(AccountID, var)
Dim created() As EntitySuccessType
created = result.SuccessRow
Dim i As Integer
For i = LBound(created) To UBound(created)
'Store the newly created accountIDS
</prev>

So I attempted to return an array of ID's to work around the documented
problem. While debugging the .NET, I can see my array being built I just
cannot wrap my brain around how to pass this back to my VBA - calling routine
from this VB.NET function.