The following problem occurs:

In an EXCEL-VBA module, I declare an array of some 50000 longs. I fill the array with values. When I access the array from within the module, everything is fine - but if another module accesses these values (via API-functions), it seems that the values are gone. With a smaller array, say 5000 longs, the problem doesn't occur: then the values are pertained (as they should).

My hypothesis is, that it depends on whether or not modules are unloaded to disk because of memory limitations.

So: it seems that VBA does not pertain values of module-level variables when modules are swapped in and out virtual memory.

Does anyone know about this problem?

Any help is appreciated!

Thanks,
Kees.

How and where are you declaring your array?

I declare the array in the top of the module, outside any of the functions or subs in the module, as

dim a(50000) as long


K.

That's your problem. Don't use DIM as that limits it to that module. Use

Public a...

I changed the 'dim' to 'public', but unfortunately the behavior stays the same. One further thing that seems to be part of the problem: if I lower the dimension array, the problem seems to be gone ... however, as soon as I modify (edit) the VBA code it falls back to the old problem (i.e., the data is lost when calling a function from another module). However if I then quit EXCEL and open it again (with the same .xls document), everything is fine ... until I do the next edit in VBA. In other words, I can get my application running, but every time when I change something in the code I have to completely quit EXCEL and start it again. Isn't this weird?

Thanks,

K.