I've come across a peculiar problem in VBA for Excel which I ran into when migrating some client code for a UDF so that it could be placed in an XLL instead of as a macro. That said, I've tested the example both as a macro and in an XLL and the problem occurs in both use cases.
The easiest way to explain it is to give a code example that reproduces the problem:
1 Public Function HP()
2 Dim i As Long, k As Long
3 Dim HP1(2, 2)
4 For i = 0 To 1
5 For k = 0 To 1
6 HP1(k, i) = k + i + 10
7 Next k
8 Next i
9 HP = HP1
10 End Function
This produces an array result which spills and shows #VALUE errors in the entries for the upper two rows and the leftmost two columns.
However, if the variables i and j are declared as Integer, this does not happen.
Also if line 6 is replaced by
(or any constant), the code works.
It took me a while to recreate the problem in its simplest form (and to figure out what was provoking the #VALUE errors) because the array itself is fixed size and dimensioned using constants. I'd guess that the compiler is trying to anticipate the risk that a large number will be assigned to the array elements in question. However It does not seem to help to dimension HP() as Long.
So I can't see why the behaviour of the array should depend on the dimension of i and j, which are only used in the For loop.
I can 'work around' the issue after a fashion by declaring i and j to be Integer, but this will restrict the functionality of the final code