VBA修复溢出行

Question

我是VBA的超级新手。我正在处理一个问题，我正在循环并创建一个句子，但我遇到了一个溢出行的问题。你能解释一下我哪里出错了吗？

Sub clue()
Dim name, room, weapon As String
Dim can, dag, lead, rev, rop, wre, total, least As Double
Dim row As Integer


Cells(1, 1).Activate
cam = 0
dag = 0
lead = 0
rev = 0
rop = 0
wre = 0
row = 1

Do Until IsEmpty(ActiveCell)

name = ActiveCell.Value
room = ActiveCell.Offset(1, 0).Value
weapon = ActiveCell.Offset(2, 0).Value


Cells(row, 3).Value = name & " in the " & room & " with the " & weapon & "."

If weapon = "Candlestick" Then
    can = can + 1
End If
If weapon = "Dagger" Then
    dag = dag + 1
End If
If weapon = "Lead Pipe" Then
    lead = lead + 1
End If
If weapon = "Revolver" Then
    rev = rev + 1
End If
If weapon = "Rope" Then
    rop = rop + 1
End If
If weapon = "Wrench" Then
    wre = wre + 1
End If
ActiveCell.End(xlDown).End(xlDown).Activate
row = row + 1
Loop

total = can + dag + lead + rev + rop + wre
Cells(2, 6) = can
Cells(3, 6) = dag
Cells(4, 6) = lead
Cells(5, 6) = rev
Cells(6, 6) = rop
Cells(7, 6) = wre

Cells(2, 7) = can / total
Cells(3, 7) = dag / total
Cells(4, 7) = lead / total
Cells(5, 7) = rev / total
Cells(6, 7) = rop / total
Cells(7, 7) = wre / total

least = 1000000000
If can < least Then least = can
If dag < can Then least = dag
If lead < dag Then least = lead
If rev < lead Then least = rev
If rop < rev Then least = rop
If wre < rop Then least = wre

Cells(10, 5) = least
End Sub

我试图使用某些输入在一行上打印出一句话，但随着输入的改变，我想在下一行打印下一句话(因此是row=row+1)，但它总是说有一个“溢出”问题，我需要更改一些东西，但我不知道为什么。有人知道吗？

谢谢!

Answer 1

一旦读取了32K行，您可能会出现溢出

这是因为您将row变量声明为integer。

将其更改为long，您将能够处理20亿行

Dim row As Long

试着记住这个：

Byte        between 0 and 255.
Integer     between -32,768 and 32,767.
Long        between – 2,147,483,648 and 2,147,483,647.
Currency    between -922,337,203,685,477.5808 and 922,337,203’685,477.5807.
Single      between -3.402823E38 and 3.402823E38.
Double      between -1.79769313486232D308 and 1.79769313486232D308.

Answer 2

Row返回Long，而不是Integer

请注意，Dim name, room, weapon As String

只将weapon定义为string，其余的定义为variant。

正确的语法是

Dim name as string, room as string, weapon As String

Answer 3

我喜欢这个“游戏”目的问题，既然您宣称自己是“VBA的超级新手”，我认为它可以帮助您对初始代码进行以下重构

Option Explicit

Sub clue()

Dim can As Long, dag As Long, lead As Long, rev As Long, rop As Long, wre As Long, row As Long
Dim weapon As String
Dim roomsRng As Range, areaRng As Range, roomsReportRng As Range, finalStatsRng As Range, leastStatsRng As Range ' these are useful range variable. you'll set them and use to avoid loosing control over what you're actually handling

' here follows a section dedicated to setting relevant "ranges". this helps a lot in avoiding loosing control over what you're actually handling
With ActiveSheet 'always explicitly qualify which worksheet do you want to work with. "ActiveSheet" is the currently active one, but you may want to qualify 'Worksheets("MySheetName")'
    Set roomsRng = .Range("A1:A" & .cells(.Rows.Count, 1).End(xlUp).row) 'set roomsRng range as the one collecting activesheet cells in column "A" down to the last non empty one
    Set roomsRng = roomsRng.SpecialCells(xlCellTypeConstants, xlTextValues) 'select only non blank cells of "roomsRng" range (skip blanks)

    Set roomsReportRng = .cells(1, 3) ' set the range you start writing rooms report from
    Set finalStatsRng = .Range("F2") ' set the range you'll start writing final stats from
    Set leastStatsRng = .Range("E10") ' set the range you'll write the least found weapon number in
End With

For Each areaRng In roomsRng.Areas 'loop through all "Areas" of "roomsRng" range cells: an "Area" is a group of contiguous cells
    Call WriteRoomsReport(areaRng.cells, roomsReportRng, row, weapon) 'write room report
    Call UpdateWeaponsStats(weapon, can, dag, lead, rev, rop, wre) ' update weapons statistics
Next areaRng

Call WriteFinalStats(can, dag, lead, rev, rop, wre, finalStatsRng, leastStatsRng) ' write final statistics

End Sub



Sub WriteRoomsReport(roomCells As Range, reportCell As Range, row As Long, weapon As String)
Dim arr As Variant 'it'll be used as an array, see below

arr = Application.Transpose(roomCells) 'initialize the Variant as an array, filling it up with "roomCells" range content

reportCell.Offset(row).Value = arr(1) & " in the " & arr(2) & " with the " & arr(3) & "." 'write the report line

weapon = arr(3) ' store the weapon value to pass back to calling sub

row = row + 1 'update the row for subsequent use

End Sub



Sub UpdateWeaponsStats(weapon As String, can As Long, dag As Long, lead As Long, rev As Long, rop As Long, wre As Long)

' use "Select Case" pattern to avoid multiple and unuesful If-then repetition
' once a "case" is hit, its correspondant statements will be processed and then control passes to the statement following the "End Select" one
Select Case weapon
    Case "Candlestick"
        can = can + 1
    Case Is = "Dagger"
        dag = dag + 1
    Case "Lead Pipe"
        lead = lead + 1
    Case Is = "Revolver"
        rev = rev + 1
    Case "Rope"
        rop = rop + 1
    Case Is = "Wrench"
        wre = wre + 1
End Select

End Sub



Sub WriteFinalStats(can As Long, dag As Long, lead As Long, rev As Long, rop As Long, wre As Long, finalStatsRng As Range, leastStatsRng As Range)

Dim total As Long, least As Long
Dim weaponArr As Variant

total = can + dag + lead + rev + rop + wre
weaponArr = Array(can, dag, lead, rev, rop, wre)
With finalStatsRng.Resize(6) ' select a range of 6 cells in one clolumn, starting from the passed "finalStatsRng" range and resizing it up to enclose the subsequent 5 cells below it
    .Value = Application.Transpose(weaponArr) ' fill the selected range (using ".Value" property of the "Range" object) with the "array" technique
    With .Offset(, 1) ' shift one column to the right of selected range
        .FormulaR1C1 = "=RC[-1]/" & total ' write in all cells a formula that takes the value form the adjacent cell and divide it by the "total" variable value
        .Value = .Value ' have formulas replaced with values. you can comment this and cells will remain with formulas (they show the resulting values, but if you select one of them you'll see a formula in the formula ribbon of Excel UI
    End With
End With

leastStatsRng.Value = Application.WorksheetFunction.Min(weaponArr) 'get the minimum value of all passed values calling the "MIN" function (which belongs to "WorksheetFuncion" object of the "Application" object -Excel) over the array filled with weapon countings

End Sub

上面的代码模式有以下目标：

• 将代码分解为特定的函数或子函数

为了让你们更好地通过"main“子语句(应该是"Call DoThis()”、"Call DoThat()“等语句序列)更好地控制代码流，并专注于特定的subs/功能来处理特定的工作

因此，

• 只使用一些(众多)相关的VBA和Excel VBA技术，如使用Range对象(请参阅Resize()、Offset()、End()、SpecialCells()方法)、数组(通过Variant类型变量)、WorksheetFunction对象等，就可以得到更易于维护和“可调试”的代码。

当然，您需要学习所有这些技术(以及许多其他技术！)利用这样的资源，如SO本身，MSDN站点(https://msdn.microsoft.com/en-us/library/office/ee861528.aspx)和许多其他你很容易在网络上获得的资源，只需用谷歌搜索一个重要的问题

作为最后(也是悲哀的)说明，我必须警告你:构建一个游戏最终会导致“真正的”OOP，就像VBA一样。

如果“构建游戏”是您的真正目标，那么您最好立即切换到一些真正的OOP语言，如C#，以及相应的集成开发环境，如Visual Studio (其Community Edition版本目前是免费的)