Excel 2010中VBA中行和列偏移量的最大值是多少？ [英] What is the maximum value for row and column offsets in VBA for Excel 2010?

问题描述

我知道一个类似的问题已经在这里回答：

但是，该错误似乎与Excel 2003中工作表中的行数相关。由于我运行的是Excel 2010（拥有65,536行），我不认为我的错误是相关的到我的工作表大小。

这是给我的代码行运行时错误'6'溢出：

+ code> Range（OutputStart）。Offset（1 +（iCounter1 - 1）* iDataPoints * 26 + iCounter2 * iDataPoints，0）.PasteSpecial Paste：= xlPasteValues，Operation：= xlNone，SkipBlanks：= False，Transpose：= True

iCounter1是一个整数，其值为13
iDataPoints是一个整数，其值为103
icounter2是一个整数，其值为7

行偏移计算的结果为32,858，表示整数数据类型问题。行偏移量是否限于整数数据类型？如果是这样，有没有办法将该限制改为Long（或另一种数据类型）？

感谢您提供任何帮助！

将

解决方案

不要使用整数：它们是16位有符号值< /强>;他们的范围是-32768到32767.这就是为什么你得到溢出。

使用长而不是。

I know a similar question has been answered here:

What is the maximum value for row and column Range offset in VBA/Excel?

However, that error seemed to be related to the number of rows in the worksheet in Excel 2003. Since I am running Excel 2010 (with 65,536 rows), I don't think my error is related to my worksheet size.

Here is the line of code that is giving me Run-time error '6' Overflow:

Range("OutputStart").Offset(1 + (iCounter1 - 1) * iDataPoints * 26 + iCounter2 * iDataPoints, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

iCounter1 is an integer and its value is 13 iDataPoints is an integer and its value is 103 icounter2 is an integer and its value is 7

The result of the row offset calculation is 32,858, which indicates an Integer data type issue. Is the row offset limited to Integer data types? If so, is there any way to change that limitation to Long (or another data type)?

Thanks for any help you can provide!

Will

解决方案

Don't use Integer: they are 16 bit signed values; their range is -32768 to 32767. That is why you're getting the overflows.

Use a Long instead.

这篇关于Excel 2010中VBA中行和列偏移量的最大值是多少？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！