p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Code works in Excel VBA but not Access VBA (http://p2p.wrox.com/showthread.php?t=58058)

fossx May 18th, 2007 11:02 AM

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?[?]


mmcdonal May 21st, 2007 07:59 AM

I am not sure I see the difference between this code:

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

and this code:

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

Be that as it may, Access wants you to Dim the array, like this:

Dim MyArray(i)

Where i is some known value. You could do this by either counting the value before you dim the array, or, normally what I will do is this sort of thing, which won't work for you, but you see how it wants to work:

i = 1
Do Until [some condition]
   ReDim Preserve MyArray(i)
   Add some value
   i = i + 1
Loop

Did that help any?




mmcdonal

mmcdonal May 21st, 2007 08:00 AM

Alternatively, you can also take the string that is returned that you are getting now, and use Split() to get it manually into an array.



mmcdonal


All times are GMT -4. The time now is 04:41 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.