Peculiar behaviour of Long variable type in Excel VBA

Alan Freeman 151 Reputation points
2022-05-18T21:15:27.05+00:00

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

6 HP1(k,i)=10

(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

Regards
Alan

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,484 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alan Freeman 151 Reputation points
    2022-05-19T01:51:56.537+00:00

    Thanks Viorel-1

    The reason that I augmented the dimensions was to show what the problem is. If you check the code by running it, you will see that the unused row and column cells report correctly. The problem is that the first two rows and columns report a #VALUE error, while the others do not.

    This proves that the problem is with the For loop that assigns values to the used cells.

    The problem doesn't go away if you eliminate the unused rows and columns.

    That's my point.

    I could make the same point in a different way by restricting the dimensions, but my concern is that, if I exhibited the problem in this way, then attention would be focussed on the function return mechanism (because every entry in the returned range would show the same error). But the problem is not the return mechanism. The problem is that, regardless of the return implementation, the cells which have been changed by the For loop report a #VALUE error, but the others do not.

    Cheers
    A

    Cheers
    A

  2. Alan Freeman 151 Reputation points
    2022-05-19T16:39:31.057+00:00

    I can maybe attach the spreadsheet. However I don't often use this forum, so I don't know how to do that. Do you know if that is possible? If not, I can show with screenshots and this will maybe help. I could alternatively put it in OneDrive and post a link to it here.

    We may have different Excel versions. Mine is a subscription to Microsoft 365 and it describes itself as Version 2206 (Build 15313.20000 Click-to-Run). It's a 64-bit product. I am running Windows 10

    A

    0 comments