Code works in Excel VBA but not Access VBA? 2

[fossx]

I am running Office 2003 which has VBA Retail 6.4.8869 running for both Excel and Access. The following code works just fine in Excel but gives a "Compile Error: Can't assign to array" error in Access. It is a DDE request that returns an array.

Function doRequest(serverName, topic, request) As Variant()
Dim chan As Integer
chan = Application.DDEInitiate(serverName, topic)
doRequest = Application.DDERequest(chan, request)
Application.DDETerminate chan
End Function

In Excel, I get an array returned. In access I get the compile error associated with the DDERequest line.

If I change the code to the following, I get two different behaviors out of Excel and Access (note the change is that I just specify a pure variant vs. a dynamic variant array for doRequest).

Function doRequest(serverName, topic, request) As Variant
Dim chan As Integer
chan = Application.DDEInitiate(serverName, topic)
doRequest = Application.DDERequest(chan, request)
Application.DDETerminate chan
End Function

If I make this change, I don't get the compile error in Access. However, Excel still returns a full array. However, Access only returns a string of the values.

It is the same VBA on the same computer. Any ideas why this is happening?

 

[SkipVought]

From EXCEL Help

DDERequest Method
See AlsoApplies ToExampleSpecificsRequests information from the specified application. This method always returns an array; for more information, see the example.

expression.DDERequest(Channel, Item)
expression Optional. An expression that returns an Application object.

from ACCESS Help

The DDERequest function returns a Variant as a string containing the requested information if the request was successful.

Skip,

_[red][/red]

 

[PHV]

It is the same VBA
Yes, but you're comparing methods of different objects (as outlined by skip).
In Excel VBA: Excel.Application.DDERequest
In Access VBA: Access.Application.DDERequest

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fossx]

Replaced code in my access module with:

Excel.Application.DDERequest

Everything worked fine. Thanks.