VBA间歇性ByRef错误-格式功能 [英] VBA Intermittent ByRef Error - Format function

问题描述

我对VBA的了解比对VB的了解要新.我在使用ADO.NET时遇到过项目损坏的情况，由于缺乏更好的说法，您中断了您的项目.此过程来自一个较大的项目，该项目汇总了有关呼叫中心代理性能的自动报告，特别是将找到最近7天的结果，然后删除该范围内的任何其他值，以由工作簿中的另一张纸进行计算.

I'm newer to VBA than VB. I've had circumstances while using ADO.NET where the project gets corrupted and you, for lack of a better word, break your project. This procedure is from a larger project that pulls together automated reports for call center agent performance and specifically will find the last seven days results then delete any other values within that range to be calculated by another sheet in the workbook.

背景故事:我已经使用此程序大约三个星期了，没有问题.有一天，我在运行报表时，默认格式功能(尤其是变量"d")出现ByRef错误.尝试了一堆东西来围绕format函数重写，以防语法有点不对劲:Format([string]，"Short Date")，Format([date]，"Short Date")，Format([date]，"mmddyyyy")和Format([string]，"mmddyyyy")均导致ByRef错误.我希望有一个简单的.toshortdatestring像VB.NET.试图创建自己的格式功能也无济于事.

Backstory: I've used this procedure for about three weeks with no issue. One day, I was running my reports and got a ByRef error for the default Format function, specifically the variable "d". Tried a bunch of stuff to rewrite around the format function in case the syntax was a little off: Format([string], "Short Date"), Format([date], "Short Date"), Format([date], "mmddyyyy"), and Format([string], "mmddyyyy") all result in ByRef Error. I wish there was a simple .toshortdatestring like VB.NET. Tried creating my own format function as well to no avail.

但是，当我将所有代码-完全-粘贴到我的一个备份中时，ByRef错误消失了…….以为我刚刚破坏了该工作簿，所以我将所有模块复制并粘贴到了备份中并以我快乐的方式前进.一周后，再次运行报表，在Format函数中出现相同的ByRef错误，突出显示了变量d.关于为什么这种情况持续发生的任何想法?在此先感谢！

However, when I paste all of my code - EXACTLY - into one of my backups, the ByRef error goes away.... Thought I just broke that workbook, so I copied and pasted all of my modules into my back up and went on my merry way. A week later, running my reports again, I get the same ByRef error at the Format function, highlighting the variable d. Any thoughts as to why this keeps happening?? Thanks in advance!!

Excel 2013-文件大小约为7 MB-28张-14个带有多个countifs/sumifs的文件，可从原始数据中提取特定的代理统计信息-没什么花哨的.

Excel 2013 - file size approx 7 MB - 28 sheets - 14 with multiple countifs/sumifs to pull specific agent stats from the raw data - nothing fancy.

Sub Last7Days(lastcolumn As String, wksht As Worksheet, width As Integer, datasheet As String)


Dim sht As Worksheet
Dim column As Long
Set sht = wksht

Dim rng As Range, inclusiveRange As Range
Dim startDate As Long, endDate As Long


column = 1
Dim d As Date



d = DateAdd("d", -7, Now)

d = Format(d, "Short Date")

Dim startdatestring As String


startdatestring = CStr(d)

Dim enddatestring As String

wksht.Activate
Call LastRowInA

Range("a" & LastRowInA).Select
enddatestring = CStr(ActiveCell.value)



startDate = DateValue(startdatestring)
endDate = DateValue(enddatestring)

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

wksht.Activate
    sht.Cells(1, column).AutoFilter Field:=column, Criteria1:=">=" & startDate, Operator:=xlAnd _
            , Criteria2:="<=" & endDate


    Set rng = sht.Range(sht.Cells(2, column), sht.Cells(sht.Cells(sht.Rows.Count, column).End(xlUp).Row, column)).SpecialCells(xlCellTypeVisible)

    sht.AutoFilterMode = False

    If rng.Address = sht.Cells(1, column).Address Then



        MsgBox Format(startDate, "dd-mmm-yyyy") & " - " & Format(endDate, "dd-mmm-yyyy") _
        & vbCrLf & vbCrLf & "No instances of the date range exist"

    Else

    Set inclusiveRange = sht.Range(rng.Cells(1, 1), rng.Cells(rng.Count, width))

        inclusiveRange.Select
        Selection.Cut
        ActiveSheet.Paste Destination:=Worksheets(datasheet).Range("a2")
        Dim Start As String

        Dim size As Long
        'Set size = Nothing
        Start = "A" & (rng.Count + 1)
        size = Range("a" & rng.Count, Range("a" & rng.Count).End(xlDown)).Rows.Count
       Range("a" & (rng.Count + 1) & ":I675000").Select
       Selection.Clear



    End If
Dim LastDateValue As String

LastDateValue = enddatestring


startdate1 = DateValue(LastDateValue)
endDate1 = DateValue(LastDateValue)

Dim testDate As Date
Dim testDateInteger As Integer
testDate = startdate1
testDateInteger = Weekday(testDate)

If testDateInteger = 2 Then


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

wksht.Activate
    sht.Cells(1, column).AutoFilter Field:=column, Criteria1:=">=" & startdate1, Operator:=xlAnd _
        , Criteria2:="<=" & endDate1


    Set rng = sht.Range(sht.Cells(2, column), sht.Cells(sht.Cells(sht.Rows.Count, column).End(xlUp).Row, column)).SpecialCells(xlCellTypeVisible)

    sht.AutoFilterMode = False


    Set inclusiveRange1 = sht.Range(rng.Cells(1, 1), rng.Cells(rng.Count, width))

        inclusiveRange1.Select
        Selection.Clear
  End If


End Sub

推荐答案

目前尚不清楚，这是事实，但很可能是注释中的对话所表明的是编译器错误，这很可能是超级错误的.具体取决于您代码中功能的确切用法.要解决此问题，您可以尝试以略有不同的方式重写相同的代码逻辑.假设您的错误与以下代码段有关，我可以提出一些建议，并根据Excel对这些响应的方式提出建议.

It's not entirely clear what is going on, it's true, but odds are if it is a compiler bug as the conversation in the comments indicates, it's probably hyper-specific to the exact usage of functions you have in your code. To fix it, you could try rewriting the same code logic a slightly different way. Assuming that your error is with the following section of code, I can suggest a couple things and based on how Excel reacts to those there could be other things to try.

Dim d As Date
d = DateAdd("d", -7, Now)
d = Format(d, "Short Date")
Dim startdatestring As String
startdatestring = CStr(d)

1. 尝试将 Option Explicit 添加到代码模块的顶部.这会导致VBA强制您显式地 Dim 所有变量，虽然它可能无法解决您的特定问题，但它是一种很好的编程习惯，它可能会揭示代码中的其他问题，您可以修复并获取这些问题.

1. Try adding Option Explicit to the top of your module of code. This causes VBA to force you to explicitly Dim all your variables and while it probably won't solve your specific issue, it's good programming practice and it may reveal other issues with your code that you can fix and get out of the way.

尝试不使用变量 d 重写代码段.

Try rewriting the section of code without the variable d.

Dim startdatestring As String
startdatestring = Format(DateAdd("d", -7, Now), "Short Date")

这篇关于VBA间歇性ByRef错误-格式功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！