View Single Post
  #1 (permalink)  
Old May 18th, 2007, 11:02 AM
fossx fossx is offline
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Code works in Excel VBA but not Access VBA

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?

Reply With Quote