问题描述
我的代码在Excel 2007中正常工作,因为我转而使用excel 2010我开始得到这个运行时错误自动化错误调用的对象与其客户端断开连接,错误号 -2147417848 ,然后退出。
表格(风险详细信息)范围(A3),运行此函数后出现错误2次&:BV&(count + 1))。删除Shift:= xlUp
任何帮助我找出为什么会发生这种情况以及如何解决这个问题?
以下是函数的完整代码
函数clearData(可选刷新为布尔值)作为布尔值
Application.ScreenUpdating = False
表单(单一风险注册表)。取消保护myPass
表单(单一风险注册表)。激活
Cells.FormatConditions.Delete
Sh eets(风险详细信息)。取消保护myPass
表单(风险)。取消保护myPass
Dim currentrange作为范围
表单(风险)。激活
设置currentrange = Sheets(Risks)。Range(RisksTable)
currentrange.ClearContents
Dim count As Integer
count = Sheets(Risks)。ListObjects(RisksTable)。ListRows.count
如果count> 1然后
Sheets(Risks)。Range(A3&C&(count + 1))删除Shift:= xlUp
如果
Sheets(Risk Details)。激活
设置currentrange = Sheets(Risk Details)。范围(RiskDetails)
currentrange.ClearContents
count = (风险详细信息)ListObjects(RiskDetails)。ListRows.count
如果count> 1然后
表(风险详细信息)范围(A3&:BV&(count + 1))删除Shift:= xlUp
结束如果
表(单一风险注册表)激活
count = CInt(Range(ActionsCount))
范围(ActionsCount)= 1
Dim tableLastRow As Integer
tableLastRow = getLastTableRow()
如果(tableLastRow> = 48)然后
表单(单一风险注册表)。范围(B48&:K& tableLastRow)。删除Shift:= xlUp
End If
Range(ActionsTable [[Action Description]:[Action Commentary]])。选择
Application.CutCopyMode = False
Selection.ClearContents
Application.ScreenUpdating = False
Dim DataRange As Range
Dim o As Shape
Dim tC As Variant
Dim propertiesCount As Integer
Dim i As Integer
tC = Sheets(TableColumns)。ListObjects(TableColumns)。DataBodyRange
propertiesCount = GetTemplatePropertiesCount(templates(0)) - 1
ReDim属性(0到propertiesCount - 1,0到4)
If( (i + 1)(propertiesCount,2)= templates(0))然后
properties = properitesColl.Item(i + 1)
End If
对于计数= 0到propertiesCount - 1
如果((properties(count,4)< C25)And(properties(count,4) - C26)And(properties(count,4) - C27)And(properties(count,4) (属性(count,4) - C38)和(属性(count,4) - C39)和(属性(count,4) - C40)和(属性(count,4) - C41))
然后
表单(单一风险注册表)范围(属性(count,4))=
End If
Next
Dim columnCount As Integer
columnCount = GetColumnCount()
count = 0
For i = 1 To columnCount
如果tC(i,4)<> Action然后
设置currentrange = Range(tC(i,4))
Else
设置currentrange = Range(ActionsTable [& tC(i,1)&] )
End If
如果((tC(i,4)=C25)或(tC(i,4)=C26)或(tC(i,4)=C27 )(tC(i,4)=C28)或(tC(i,4)=C38)或(tC(i,4)=C39)或(tC(i,4)= C40)或(tC(i,4)=C41))
然后
With currentrange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15654866
.TintAndShade = 0
.PatternTintAndShade = 0
End with
Else
With currentrange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
结束
结束If
下一个
Shee ts(单一风险注册表)。取消保护myPass
如果(Range(C3)。Value =)然后
Range(ActionsTable [[Action Description]:[& addTitle& ]])。选择
Selection.Locked = True
Rows(45:47)。选择
Selection.EntireRow.Hidden = True
End If $ b $值为Range(TitleCell))Value = Range(OldTitle)+(+ Range(Project_Title)+)
表单(单一风险注册表)。保护myPass,AllowFiltering:= True
表单(风险详细信息)。保护myPass,AllowFiltering:= True
Risks)。保护myPass,AllowFiltering:= True
doNotRun = False
范围(C3)选择
Application.ScreenUpdating = True
退出功能
错误:
表格(风险详细信息)。保护myPass
表单(风险)。保护myPass
MsgBox err.Description,vbCritical,Error
:
如果代码总是出现在第一次运行的时候, d如果错误或意外行为仅在后续调用相同的代码时发生,则不合格的方法调用就是原因。
I am working on clearing some tables in 3 excel sheets.
My code was working fine with Excel 2007, as i switched to excel 2010 i started getting this runtime error Automation error The object invoked has disconnected from its clients, error number -2147417848 and then excel freezes.
the error is showing after running this function 2 times on the line
Sheets("Risk Details").Range("A3" & ":BV" & (count + 1)).Delete Shift:=xlUp
can anyone help me in finding out why this is happening and how to work around it?
below are the full code for the function
Function clearData(Optional refresh As Boolean) As Boolean
Application.ScreenUpdating = False
Sheets("Single Risk Register").Unprotect myPass
Sheets("Single Risk Register").Activate
Cells.FormatConditions.Delete
Sheets("Risk Details").Unprotect myPass
Sheets("Risks").Unprotect myPass
Dim currentrange As Range
Sheets("Risks").Activate
Set currentrange = Sheets("Risks").Range("RisksTable")
currentrange.ClearContents
Dim count As Integer
count = Sheets("Risks").ListObjects("RisksTable").ListRows.count
If count > 1 Then
Sheets("Risks").Range("A3" & ":C" & (count + 1)).Delete Shift:=xlUp
End If
Sheets("Risk Details").Activate
Set currentrange = Sheets("Risk Details").Range("RiskDetails")
currentrange.ClearContents
count = Sheets("Risk Details").ListObjects("RiskDetails").ListRows.count
If count > 1 Then
Sheets("Risk Details").Range("A3" & ":BV" & (count + 1)).Delete Shift:=xlUp
End If
Sheets("Single Risk Register").Activate
count = CInt(Range("ActionsCount"))
Range("ActionsCount") = 1
Dim tableLastRow As Integer
tableLastRow = getLastTableRow()
If (tableLastRow >= 48) Then
Sheets("Single Risk Register").Range("B48" & ":K" & tableLastRow).Delete Shift:=xlUp
End If
Range("ActionsTable[[Action Description]:[Action Commentary]]").Select
Application.CutCopyMode = False
Selection.ClearContents
Application.ScreenUpdating = False
Dim DataRange As Range
Dim o As Shape
Dim tC As Variant
Dim propertiesCount As Integer
Dim i As Integer
tC = Sheets("TableColumns").ListObjects("TableColumns").DataBodyRange
propertiesCount = GetTemplatePropertiesCount(templates(0)) - 1
ReDim properties(0 To propertiesCount - 1, 0 To 4)
If (properitesColl.Item(i + 1)(propertiesCount, 2) = templates(0)) Then
properties = properitesColl.Item(i + 1)
End If
For count = 0 To propertiesCount - 1
If ((properties(count, 4) <> "C25") And (properties(count, 4) <> "C26") And (properties(count, 4) <> "C27") And (properties(count, 4) <> "C28") And (properties(count, 4) <> "C38") And (properties(count, 4) <> "C39") And (properties(count, 4) <> "C40") And (properties(count, 4) <> "C41"))
Then
Sheets("Single Risk Register").Range(properties(count, 4)) = ""
End If
Next
Dim columnCount As Integer
columnCount = GetColumnCount()
count = 0
For i = 1 To columnCount
If tC(i, 4) <> "Action" Then
Set currentrange = Range(tC(i, 4))
Else
Set currentrange = Range("ActionsTable[" & tC(i, 1) & "]")
End If
If ((tC(i, 4) = "C25") Or (tC(i, 4) = "C26") Or (tC(i, 4) = "C27") Or (tC(i, 4) = "C28") Or (tC(i, 4) = "C38") Or (tC(i, 4) = "C39") Or (tC(i, 4) = "C40") Or (tC(i, 4) = "C41"))
Then
With currentrange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15654866
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Else
With currentrange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
End If
Next
Sheets("Single Risk Register").Unprotect myPass
If (Range("C3").Value = "") Then
Range("ActionsTable[[Action Description]:[" & addTitle & "]]").Select
Selection.Locked = True
Rows("45:47").Select
Selection.EntireRow.Hidden = True
End If
Sheets("Single Risk Register").Range(Range("TitleCell")).Value = Range("OldTitle") + " (" + Range("Project_Title") + ")"
Sheets("Single Risk Register").Protect myPass, AllowFiltering:=True
Sheets("Risk Details").Protect myPass, AllowFiltering:=True
Sheets("Risks").Protect myPass, AllowFiltering:=True
doNotRun = False
Range("C3").Select
Application.ScreenUpdating = True
Exit Function
err:
Sheets("Risk Details").Protect myPass
Sheets("Risks").Protect myPass
MsgBox err.Description, vbCritical, "Error"
Microsoft provide a high-level answer:
If the code always appears to work the first time that it is run, and if the errors or the unexpected behaviors occur only during subsequent calls to the same code, an unqualified method call is the cause.
这篇关于错误-2147417848自动化错误调用的对象与其客户端断开连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!