question

AlanFreeman-2854 avatar image
0 Votes"
AlanFreeman-2854 asked AlanFreeman-2854 answered

Peculiar behaviour of Long variable type in Excel VBA

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-vba-devoffice-scripts-excel-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


How to use this function to see the #VALUE problem? A formula like '=HP( )' seems to work.

The dimensions can be changed to 'Dim HP1(1, 1)' since the third row and column are not used.

0 Votes 0 ·
AlanFreeman-2854 avatar image
0 Votes"
AlanFreeman-2854 answered Viorel-1 commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


How to use this function to see the #VALUE problem?


0 Votes 0 ·

Here's what I did:
Create a new blank spreadsheet
Open Visual Basic (I used the Developer Tab but first had to add this to the Ribbon using Microsoft's instructions)
Right Click on 'Sheet1' and select 'View Code'
An editing window opens in the righthand pane
Paste the code into that window and save (as XLSM because it has a macro in it)
Go back to the Spreadsheet
in any cell, type '=HP()' and Enter
What then shows is
203744-image.png

Hope this helps. I can send the spreadsheet if that would help
Cheers
A


0 Votes 0 ·
image.png (4.9 KiB)
Viorel-1 avatar image Viorel-1 AlanFreeman-2854 ·

The problem was not reproduced with these steps.


0 Votes 0 ·
AlanFreeman-2854 avatar image
0 Votes"
AlanFreeman-2854 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.