Excel crashes when I add VBA code programatically

[mdgabriel0616]

I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:

ThisWorkbook.VBProject.VBComponents.item(sCodeName).CodeModule.AddFromString
s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.

 

[Chip Pearson]

Does the code that performs the updates reside in the same module as the
code being inserted? In other words, is a module attempting to modify
itself? I would STRONGLY recommend against doing so. If you have the time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project should
only update another project, not itself.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Matt]

The code that is performing the update does not reside in the same
module, but is part of the same VB project. This is really kind of
the intent as I essentially want users to be working with the latest
code without having to go somewhere else to get the update. I suppose
I could make the Update procedure an add-in but that would then
require my users to install something else and it defeats the purpose
to some extent.

As it's working right now, the code that is running the update is
completely untouched by the update itself. It's iterating through a
fixed list of modules, deleting all the lines in the CodeModule, then
adding the code back into this set list of modules via a string
returned from the XML web service. Again, this is working for some of
my modules and Excel is crashing on others. If I remove the modules
that cause Excel to crash, the process works. Would Excel crash
because there are references to classes or types I have defined or
could whitespace cause it? Would Excel crash because the code cannot
be compiled until all of the code is added back in? Speaking of
which, is there a way recompile the code once it's added back in?
Would Excel crash because there are cells in the workbook that are
calling functions from VBA that are deleted and then added back in?

Thank you for your help.

Matt

 

[Peter T]

Comments inline -

The code that is performing the update does not reside in the same
module, but is part of the same VB project. This is really kind of
the intent as I essentially want users to be working with the latest
code without having to go somewhere else to get the update.

I'm sure there's a reason but how would an 'old' project modify itself with
the latest code. IOW where would it get the code from, presumably some other
source so why can't 'some other source' be a new project that updates the
old project.

I suppose
I could make the Update procedure an add-in but that would then
require my users to install something else and it defeats the purpose
to some extent.

You could have code in your old project that runs manually (button click) or
automatically (every x weeks) to get the update and triggers it to do its
thing.

As it's working right now, the code that is running the update is
completely untouched by the update itself. It's iterating through a
fixed list of modules, deleting all the lines in the CodeModule, then
adding the code back into this set list of modules via a string
returned from the XML web service. Again, this is working for some of
my modules and Excel is crashing on others. If I remove the modules
that cause Excel to crash, the process works.

Curiosity, is it writing to ordinary modules or code behind object modules
that causes your crash.

Would Excel crash
because there are references to classes or types I have defined or
could whitespace cause it?

Not sure but it would be worthwhile clearing any references in advance.

Would Excel crash because the code cannot
be compiled until all of the code is added back in? Speaking of
which, is there a way recompile the code once it's added back in?

Best avoid that altogether, indeed it might be your project compiling
inadvertantly while the code is running that is causing your Excel to crash.

If you wanted to re-compile some other project there is a cludgy way but
there's probably no need to recompile. As user runs code from different
modules those modules will compile, if it's an xls it willl get saved in its
latest compile state.

FWIW, programatically adding ActiveX worksheet controls in the same workbook
that's running the code will very likely crash Excel.

Would Excel crash because there are cells in the workbook that are
calling functions from VBA that are deleted and then added back in?

Probably not. If you are forcing a calculation during an update, which I
don't suppose your are, the worse that's likely to happen is a #NAME? error
in cells. Unless perhaps the function exists but is doing something
internally that fails during the update.

I think it would be well worth your while to pay head to the advice that
Chip Pearson gave you.

Regards,
Peter T

 

[Jon Peltier]

As far as I'm concerned, no good can come of programmatic manipulation of
code. One unmentioned issue is that if the modules are particularly large (>
64k or so) they may become unstable.

You would do well to move the code into an add-in, and introduce a protocol
that replaces the old add-in by a newer one. Jan Karel Pieterse has some how
to examples on his web site (http://jkp-ads.com) that show how to get
started using add-ins and how to update them programmatically.

- Jon

 

[Matt]

First, thank you for your input.

As you suggested, I took Chip's advice and moved the entire update
process to its own Add-In. So now it's in its own VB Project,
updating the original. It's still crashing in exactly the same place
when updating the same module. Very frustrating.

My update process updates code behind the forms, modules and class
modules. The problem, surprisingly enough, happens when updating a
particular module.

I seem to be getting the feedback from the Microsoft MVPs that
updating code is a bad idea... but if that's the case, why are these
functions available in Excel? Why is there CodeModule.AddFromString
if it's so unstable that Microsoft is recommending against using it?

The concept here is to proactively update my users' functionality in
an Excel workbook that they have customized on their own. Their
changes will be to the worksheets. My changes are to the underlying
code. I don't want to lose their changes (which, of course, differ by
user), but I do want to push my changes into their files. What's most
frustrating here is that this is working at a 95% level... Some
modules are updating perfectly. And the ones that are not, actually
cause Excel to crash. There HAS to be a reason that this is so
unstable with certain modules since it crashes on the same ones every
time... but I don't see what it is.

Any other ideas?

 

[Peter T]

I seem to be getting the feedback from the Microsoft MVPs that

updating code is a bad idea...

Indeed if code is updating self.

but if that's the case, why are these
functions available in Excel?

Some things might work fine writing code to "self" but I assume the methods
are primarily available for one project to update another.

...I moved the entire update
process to its own Add-In. So now it's in its own VB Project,
updating the original. It's still crashing in exactly the same place
when updating the same module.

Where is the code to start the update initiated from. If you have a small
function in the project tb updated that calls the dedicated update project,
in effect it's still updating itself and you have done nothing to solve the
problem.

If that's not the case and the update project is called independently,
hopefully there's something in particular that can be linked to the crash
and rectified. If the new code in the offending module is not too much post
as is, otherwise post anything vaguely suspicious.

Regards,
Peter T


First, thank you for your input.

As you suggested, I took Chip's advice and moved the entire update
process to its own Add-In. So now it's in its own VB Project,
updating the original. It's still crashing in exactly the same place
when updating the same module. Very frustrating.

My update process updates code behind the forms, modules and class
modules. The problem, surprisingly enough, happens when updating a
particular module.

I seem to be getting the feedback from the Microsoft MVPs that
updating code is a bad idea... but if that's the case, why are these
functions available in Excel? Why is there CodeModule.AddFromString
if it's so unstable that Microsoft is recommending against using it?

The concept here is to proactively update my users' functionality in
an Excel workbook that they have customized on their own. Their
changes will be to the worksheets. My changes are to the underlying
code. I don't want to lose their changes (which, of course, differ by
user), but I do want to push my changes into their files. What's most
frustrating here is that this is working at a 95% level... Some
modules are updating perfectly. And the ones that are not, actually
cause Excel to crash. There HAS to be a reason that this is so
unstable with certain modules since it crashes on the same ones every
time... but I don't see what it is.

Any other ideas?

 

[Matt]

I was thinking along the same lines as far as where the call to update
the code initiated. And to ensure its independence, I created a menu
item that calls the Updater add-in when clicked. The Add-In is given
a reference to the workbook to updated when the workbook to update is
opened. So... the code that is being executed is not contained within
the updating workbook at all. Here's my updating code:

Menu calls:
Private Sub menCheckForUpdate()
If Not frmPostCode.gw_WorkbookToUpdateWorkbook Is Nothing Then
CheckCodeVersion frmPostCode.gw_WorkbookToUpdateWorkbook,
frmPostCode.gs_WorkbookToUpdateCODEVERSION
Else
MsgBox "WorkbookToUpdate file is not open."
End If
End Sub


Public Function CheckCodeVersion(ByRef WorkbookToUpdateWorkbook As
Workbook, ByVal s_WorkbookToUpdateCodeVersion As String) As Boolean
Dim sRemVersion As String
sRemVersion = WorkbookToUpdateAudit.wsm_GetLatestCodeVersion
If IsRemoteVersionLater(s_WorkbookToUpdateCodeVersion,
sRemVersion) Then
'prompt user to update code
Dim sMsg As String, iBuild As Integer, sBuildDesc As String,
bUpdateable As Boolean
bUpdateable = True
sMsg = "An update to WorkbookToUpdate is available. This
update includes the following:" + vbCrLf + vbCrLf
If MajorVersionNumber(sRemVersion) =
MajorVersionNumber(s_WorkbookToUpdateCodeVersion) Then
For iBuild = BuildNumber(s_WorkbookToUpdateCodeVersion) +
1 To BuildNumber(sRemVersion)
sBuildDesc =
WorkbookToUpdateAudit.wsm_GetCodeDescription(MajorVersionNumber(sRemVersion)
+ "." & iBuild)
If sBuildDesc <> "" Then
sMsg = sMsg + vbCrLf + sBuildDesc
End If
bUpdateable = bUpdateable And
WorkbookToUpdateAudit.wsm_GetUpdateable(MajorVersionNumber(sRemVersion)
+ "." & iBuild)
Next iBuild
Else
sMsg = "An update to WorkbookToUpdate is available. This
includes a major release to WorkbookToUpdate. Please see Internet for
a description of the changes." + vbCrLf + vbCrLf
bUpdateable = False
End If

If bUpdateable Then
sMsg = sMsg + vbCrLf + vbCrLf + "Would you like to update
your WorkbookToUpdate with these changes automatically?"

Dim iResp As Integer
iResp = MsgBox(sMsg, vbYesNo + vbInformation, "Update
WorkbookToUpdate?")
If iResp = vbYes Then
UnprotectVBProject WorkbookToUpdateWorkbook,
"T0w3rs456"
GetAllCode WorkbookToUpdateWorkbook, sRemVersion
MsgBox "Done updating."
End If
Else
sMsg = sMsg + "Your WorkbookToUpdate cannot be updated
automatically. If you would like to incorporate all of the functional
changes in the latest version of WorkbookToUpdate, please download it
from Internet and rebuild your WorkbookToUpdate."
MsgBox sMsg, vbInformation + vbOKOnly, "WorkbookToUpdate
update available on Internet."
End If
Else
CheckCodeVersion = True
End If
'MsgBox "Done checking version"
End Function

Public Sub GetAllCode(ByRef WorkbookToUpdateWorkbook As Workbook,
ByVal sCodeVersion As String)
Application.Cursor = xlWait
'Dim xlCalcMode As XlCalculation
'xlCalcMode = Application.Calculation
'Application.Calculation = xlCalculationManual

Dim l As Long
For l = 1 To pSC_Modules.ModuleCount
DeleteCode WorkbookToUpdateWorkbook, pSC_Modules.ModuleName(l)
AddCode WorkbookToUpdateWorkbook, sCodeVersion,
pSC_Modules.ModuleName(l)
DoEvents
Next l

'MsgBox "Done getting code. Turn calc mode back to what it was."

'Application.Calculation = xlCalcMode
Application.Cursor = xlDefault
End Sub

Private Sub DeleteCode(ByRef WorkbookToUpdateWorkbook As Workbook,
ByVal sCodeName As String)
Application.StatusBar = "Deleting " + sCodeName + "..."

WorkbookToUpdateWorkbook.VBProject.VBComponents.Item(sCodeName).CodeModule.DeleteLines
1,
WorkbookToUpdateWorkbook.VBProject.VBComponents.Item(sCodeName).CodeModule.CountOfLines
Application.StatusBar = ""
End Sub


Private Sub AddCode(ByRef WorkbookToUpdateWorkbook As Workbook, ByVal
sCodeVersion As String, ByVal sCodeName As String)
Application.StatusBar = "Adding " + sCodeName + "..."
'MsgBox "Adding " + sCodeName + "..."
Dim s As String
s = WorkbookToUpdateAudit.wsm_GetCode(sCodeVersion, sCodeName)

WorkbookToUpdateWorkbook.VBProject.VBComponents.Item(sCodeName).CodeModule.AddFromString
s
Application.StatusBar = ""
End Sub


I will show the code from the module that is failing in a separate
post.

 

[Matt]

Here's the code that I am adding that causes Excel to crash. Note,
this is now in a Class Module... used to be just a module. Crashes
either way. Please let me know if you see anything here that looks
suspicious. Thank you.

Option Explicit
Private errMsgs As Range
Public errMsgsCount As Integer
Private errWorksheet As Worksheet
Public currentUsage As String
Private Const InputSheetName As String = "Input"
Public WorkbookToUpdateVer As String
Public SelectedPlanName As String
Public Function getfn() As Variant
Dim fileFilter As String
fileFilter = "Excel Files (*.xls), *.xls,CSV Files (*.csv),
*.csv,All Files (*.*), *.*"
getfn = Application.GetOpenFilename(fileFilter:=fileFilter,
Title:="Select File for Import")
End Function
Public Sub runImport(ByRef importInfo As ImportParams, ByVal
checkERISA As Boolean, ByVal checkFAS87 As Boolean, ByVal
checkFAS106WithSubsidy As Boolean, ByVal checkFAS106WithoutSubsidy As
Boolean, ByVal radioERISAtotal As Boolean, ByVal radioFAS87total As
Boolean, ByVal radioFAS106WithSubsidytotal As Boolean, ByVal
radioFAS106WithoutSubsidytotal As Boolean)
Dim saveUpdating As Boolean
Dim wbSummary As Workbook
ReDim cellArray(0 To 0, 0 To 0) As String 'array to hold all
information from the integration ws
ReDim errorArray(0 To 0) As String 'array to hold
warning and error messages
saveUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
'only process the usage that has been checked
If checkERISA = True Then
currentUsage = "ERISA"
If importInfo.bPPAApplies Then
'Run for post-PPA
Call copyCells(importInfo, cellArray, "ERISA PPA -
Valuation Year")
Call pasteERISAInputCells_2_00(importInfo, cellArray,
errorArray)
Call pasteERISACells_2_00(importInfo, cellArray,
errorArray)
Call pasteERISALiabCells_2_00(importInfo, cellArray,
errorArray, radioERISAtotal)
Else
'Run for pre-PPA
Call copyCells(importInfo, cellArray, "ERISA - Valuation
Year")
Call pasteERISAInputCells_1_10(importInfo, cellArray,
errorArray)
Call pasteERISACells_1_10(importInfo, cellArray,
errorArray)
Call pasteERISALiabCells_1_10(importInfo, cellArray,
errorArray, radioERISAtotal)
End If
End If
If checkFAS87 = True Then
currentUsage = "FAS87"
Call copyCells(importInfo, cellArray, "FAS87 - Valuation
Year")
Call pasteFAS87InputCells_1_10(importInfo, cellArray,
errorArray)
Call pasteFAS87Cells_1_10(importInfo, cellArray, errorArray)
Call pasteFAS87LiabCells_1_10(importInfo, cellArray,
errorArray, radioFAS87total)
End If
If checkFAS106WithSubsidy = True Or checkFAS106WithoutSubsidy =
True Then
currentUsage = "FAS106"
Call copyCells(importInfo, cellArray, "FAS106 - Valuation
Year")
Call pasteFAS106InputCells_1_10(importInfo, cellArray,
errorArray)
Call pasteFAS106Cells_1_10(importInfo, cellArray, errorArray)
Call pasteFAS106LiabCells_1_10(importInfo, cellArray,
errorArray, checkFAS106WithSubsidy, False,
radioFAS106WithSubsidytotal, radioFAS106WithoutSubsidytotal)
Call pasteFAS106LiabCells_1_10(importInfo, cellArray,
errorArray, False, checkFAS106WithoutSubsidy,
radioFAS106WithSubsidytotal, radioFAS106WithoutSubsidytotal)
End If
Call endProcessing(importInfo, errorArray)
Application.ScreenUpdating = saveUpdating
currentUsage = ""
End Sub
Public Function getImportParams(ByVal bPPAApplies As Boolean, ByVal
sIntegration_workBookName As String) As ImportParams
Dim importInfoLocal As ImportParams
importInfoLocal.sIntegration_workBookName =
sIntegration_workBookName
Set importInfoLocal.wIntegration_workbook = Nothing
importInfoLocal.bPPAApplies = bPPAApplies
getImportParams = importInfoLocal
End Function
Public Function validateFiles(ByRef importInfo As ImportParams) As
Boolean
Dim b As Boolean
validateFiles = True
'try to copy the WorkbookToUpdate file so as not to ruin the
original
' MDG 5/6/2008: Run from WorkbookToUpdate, no need to validate
WorkbookToUpdate file
' If Not saveWorkbook(importInfo) Then
' validateFiles = False
' Exit Function
' End If
'try to connect to the integration file and the newly created
WorkbookToUpdate file
b = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set importInfo.wIntegration_workbook =
workBooks.Open(importInfo.sIntegration_workBookName)
If Not canActivate(importInfo.wIntegration_workbook) Then
MsgBox ("Unable to load " &
importInfo.sIntegration_workBookName)
Set importInfo.wIntegration_workbook = Nothing
validateFiles = False
End If
' Set WorkbookToUpdateworkbook =
workBooks.Open(WorkbookToUpdateworkbook.name)
' If Not canActivate(WorkbookToUpdateworkbook) Then
' MsgBox ("Unable to load " & WorkbookToUpdateworkbook.name)
' Set WorkbookToUpdateworkbook = Nothing
' validateFiles = False
' End If
Application.DisplayAlerts = b
On Error GoTo 0
End Function
Public Function canActivate(WB As Workbook) As Boolean
Dim save As Workbook
Set save = ActiveWorkbook
On Error GoTo ActivateFailed
WB.Activate
save.Activate
canActivate = True
On Error GoTo 0
Exit Function
ActivateFailed:
save.Activate
canActivate = False
End Function
Public Function canActivateWorksheet(wsname As String) As Boolean
Dim save As Worksheet
Set save = ActiveSheet
On Error GoTo ActivateFailed
Worksheets(wsname).Activate
save.Activate
canActivateWorksheet = True
On Error GoTo 0
Exit Function
ActivateFailed:
save.Activate
canActivateWorksheet = False
End Function
'Function copyCells
'This function populates cellArray with all information in the
WorkbookToUpdate integration
'workbook. This is done so that we do not have to constantly search
the workbook,
'we can just search the array. This is done so that we don't have as
many workbook
'activations.
Public Function copyCells(ByRef importInfo As ImportParams, ByRef
cellArray() As String, Worksheetname As String)
Dim maxRow As Integer, maxColumn As Integer
Dim row As Integer, column As Integer
Dim errorMsg As String
With importInfo.wIntegration_workbook
On Error GoTo ActivateFailed
maxRow = .Worksheets(Worksheetname).UsedRange.Rows.Count
maxColumn
= .Worksheets(Worksheetname).UsedRange.Columns.Count
ReDim cellArray(0 To maxRow, 0 To maxColumn)
.Worksheets(Worksheetname).Activate
With Range("A1:A1")
For row = 0 To maxRow
For column = 0 To maxColumn
cellArray(row, column) = .Offset(row,
column).Value
Next column
Next row
End With
On Error GoTo 0
End With
Exit Function
ActivateFailed:
errorMsg = "Cannot find worksheet " + Worksheetname
errorMsg = errorMsg + " in the file " +
importInfo.sIntegration_workBookName
MsgBox (errorMsg)
End Function
Public Function findRow(ByRef cellArray() As String, findString As
String, startRow As Integer) As Integer
Dim maxRow As Integer, row As Integer
Dim notFound As Boolean
notFound = True
row = startRow
maxRow = UBound(cellArray, 1)
Do While row <= maxRow And notFound = True
If (cellArray(row, 0) = findString) Then
notFound = False
Else
row = row + 1
End If
Loop
If notFound = True Then
row = -1
End If
findRow = row
End Function
'insertField function takes the following arguments
'errorArray is the array for error messages
'cellArray holds the values from the WorkbookToUpdate Integration
worksheet
'the paste worksheet name in WorkbookToUpdate
'the field to copy is sent to the insert function as a string
'the row offset for the paste location
'the column offset for the paste location is sent via an integer
'the column offset for the copy location
'the start row for the findRow function is an optional field
Public Function insertField(ByRef errorArray() As String, ByRef
cellArray() As String, fieldName As String, rowOffsetPaste As Integer,
colOffsetPaste As Integer, colOffsetCopy As Integer, Optional startRow
As Integer = 0, Optional errorOrWarning As Boolean = True) As Integer
Dim row As Integer
row = findRow(cellArray, fieldName, startRow)
With Range("A1:A1")
If row <> -1 Then
'MDG 5/6/2008: We allow non-numerical values to be
returned
'If IsNumeric(cellArray(row, colOffsetCopy)) Then
.Offset(rowOffsetPaste, colOffsetPaste) =
cellArray(row, colOffsetCopy)
'ElseIf errorOrWarning Then
' Call addWarningMessage(errorArray, "Warning: The
value: " + cellArray(row, colOffsetCopy) + " in the " + fieldName + "
field, is not numerical.")
'End If
'if field doesn't exist, print out warning message. Unfunded
fields are conditional and may not be in the file
'The second two field names may exist multiple times, we
search and paste until not found, we don't want to display an error
for the last loop when they aren't found
ElseIf errorOrWarning And (fieldName <> "Unfunded Old
Liability" And fieldName <> "Unfunded Old Liability Amount" _
And fieldName <> "PSC Unamortized
Amount" And fieldName <> "PSC Annual Amortization" _
And fieldName <> "Years Remaining"
And fieldName <> "Unamortized Amount") Then
Call addWarningMessage(errorArray, "Warning: The field: "
+ fieldName + " was not found in the Integration Worksheet.")
End If
End With
insertField = row
End Function
'Function to create an error worksheet in the WorkbookToUpdate file.
We will hide it at first
'so the user doesn't see it until the run is complete.
Public Sub createErrorWorksheet(ByRef importInfo As ImportParams)
On Error Resume Next
WorkbookToUpdateWorkbook.Worksheets("Import Errors").Delete
On Error GoTo 0
Dim saveUpdating As Boolean
saveUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
Set errWorksheet =
WorkbookToUpdateWorkbook.Worksheets.Add(before:=Sheets(1))
errWorksheet.Name = "Import Errors"
Application.ScreenUpdating = saveUpdating
End Sub
Public Function addWarningMessage(ByRef errorArray() As String,
errorMessage As String)
If Len(Trim(currentUsage)) > 0 Then errorMessage = "[" &
currentUsage & "] " + errorMessage
errorArray(errMsgsCount) = errorMessage
errMsgsCount = errMsgsCount + 1
ReDim Preserve errorArray(0 To errMsgsCount)
End Function
'Ending processing. If there are errors, display the error worksheet
and
'set it as the active sheet. If there are no errors, delete the error
worksheet
'If there are no errors,
Public Sub endProcessing(ByRef importInfo As ImportParams, ByRef
errorArray() As String)
Dim localErrCount As Integer
localErrCount = 0
If (errMsgsCount <> 0) Then
Call createErrorWorksheet(importInfo)
errWorksheet.Activate
With Range("A1:A1")
Do While localErrCount <= errMsgsCount
If (Left(errorArray(localErrCount), 5) = "Error") Then
.Offset(localErrCount).Font.Bold = True
End If
.Offset(localErrCount, 0) = errorArray(localErrCount)
localErrCount = localErrCount + 1
Loop
End With
Else
WorkbookToUpdateWorkbook.Worksheets("Client").Activate
End If
MsgBox ("Update Completed with " + CStr(errMsgsCount) + "
errors.")
Application.StatusBar = False
End Sub
'******************************************************************************
'WorkbookToUpdate 1.10 changes
'******************************************************************************
'The redesign effor to get rid of the hardcoded stuff turned out to be
too involved
'so untill WorkbookToUpdate is rewritten just do stupid copy paste
crap
Public Function pasteFAS87Cells_1_10(ByRef importInfo As ImportParams,
ByRef cellArray() As String, ByRef errorArray() As String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "FAS" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Average Future Service", 56, 5, 1)
Call insertField(errorArray, cellArray, _
"Corridor for gain/loss amortization", 57, 5, 1)
Call insertField(errorArray, cellArray, _
"Fair Value of Assets", 60, 5, 1)
Call insertField(errorArray, cellArray, _
"Market Related Value of Assets", 61, 5, 1)
' Call insertField(errorArray, cellArray, _
' "Prepaid Pension Cost", 80, 5, 1)
Call insertField(errorArray, cellArray, _
"ITO Unamortized Amount", 64, 5, 1)
Call insertField(errorArray, cellArray, _
"ITO Annual Amortization", 64, 6, 1)
Dim startRow As Integer, pscImportRow As Integer
row = 0
startRow = 0
pscImportRow = 67 'offset for the first PSC
Do While (row <> -1) 'loop to handle multiple prior
serv costs
row = insertField(errorArray, cellArray, _
"PSC Unamortized Amount", pscImportRow, 5, 1,
startRow)
row = insertField(errorArray, cellArray, _
"PSC Annual Amortization", pscImportRow, 6, 1,
startRow)
pscImportRow = pscImportRow + 1
startRow = row + 1
Loop
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook)
End Function
Public Function pasteFAS87InputCells_1_10(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "Input" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Discount Rate", 3, 3, 1)
Call insertField(errorArray, cellArray, _
"Compensation increase - Male", 4, 3, 1)
Call insertField(errorArray, cellArray, _
"Crediting Rate", 5, 3, 1)
Call insertField(errorArray, cellArray, _
"Cost-of-living Adjustment - Post commencement", 6, 3, 1)
Call insertField(errorArray, cellArray, _
"Expected Return on Assets", 7, 3, 1)
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook.Name)
End Function
Public Function pasteFAS87LiabCells_1_10(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String, ByVal radioFAS87total As Boolean)
Dim maxColumn As Integer
Dim row As Integer, reportBreak As Integer
Dim startColumn As Integer, endColumn As Integer
Dim Worksheetname As String
reportBreak = 1
maxColumn = UBound(cellArray, 2)
If maxColumn > 5 And Not radioFAS87total Then
Call addWarningMessage(errorArray, "WorkbookToUpdate 2.0 can
handle a maximum of 3 liability groups. Your integration workbook
contains " + Trim(maxColumn - 2) + "." +
WorkbookToUpdateWorkbook.Name)
Exit Function
End If
If (radioFAS87total = True) Then
startColumn = 1
endColumn = 1
Else
startColumn = 2
endColumn = maxColumn - 1
If (startColumn > endColumn) Then
Call addWarningMessage(errorArray, "Error: Import using
Report Breaks selected for FAS87, but no Report Breaks exist in " +
importInfo.sIntegration_workBookName)
End If
End If
Do While startColumn <= endColumn
Worksheetname = "FAS" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID)) + "-
liab" + CStr(reportBreak)
If Not (canActivateWorksheet(Worksheetname)) Then
GoTo ActivateFailed
Else

WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
Application.StatusBar = "Updating " + Worksheetname
End If
Call insertField(errorArray, cellArray, _
"Total PBO", 40, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Total ABO", 41, 4, startColumn)
Call insertField(errorArray, cellArray, _
"PBO service cost (gross)", 42, 4, startColumn)
Call insertField(errorArray, cellArray, _
"ABO service cost (gross)", 43, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Expected benefit payments for year", 44, 4,
startColumn)
Call insertField(errorArray, cellArray, _
"Expected employee contributions for year", 45, 4,
startColumn)
GoTo NextColumn
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname +
" does not exist in " + WorkbookToUpdateWorkbook.Name)
NextColumn:
startColumn = startColumn + 1
reportBreak = reportBreak + 1
Loop
End Function
Public Function pasteFAS106Cells_1_10(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "FAS" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Average Future Service", 52, 5, 1)
Call insertField(errorArray, cellArray, _
"Corridor for gain/loss amortization", 53, 5, 1)
Call insertField(errorArray, cellArray, _
"AFS to Full Eligibility Age", 55, 5, 1)
Call insertField(errorArray, cellArray, _
"Fair Value of Assets", 58, 5, 1)
Call insertField(errorArray, cellArray, _
"Market Related Value of Assets", 59, 5, 1)
' Call insertField(errorArray, cellArray, _
' "Prepaid Pension Cost", 78, 5, 1)
Call insertField(errorArray, cellArray, _
"ITO Unamortized Amount", 62, 5, 1)
Call insertField(errorArray, cellArray, _
"ITO Annual Amortization", 62, 6, 1)
Dim startRow As Integer, pscImportRow As Integer
row = 0
startRow = 0
pscImportRow = 65 'offset for the first PSC
Do While (row <> -1) 'loop to handle multiple prior
serv costs
row = insertField(errorArray, cellArray, _
"PSC Unamortized Amount", pscImportRow, 5, 1,
startRow)
row = insertField(errorArray, cellArray, _
"PSC Annual Amortization", pscImportRow, 6, 1,
startRow)
pscImportRow = pscImportRow + 1
startRow = row + 1
Loop
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook.Name)
End Function
Public Function pasteFAS106InputCells_1_10(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "Input" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Discount Rate", 3, 3, 1)
Call insertField(errorArray, cellArray, _
"Compensation increase - Male", 4, 3, 1)
Call insertField(errorArray, cellArray, _
"Expected Return on Assets", 7, 3, 1)
Call insertField(errorArray, cellArray, _
"Trend rate: initial", 8, 3, 1)
Call insertField(errorArray, cellArray, _
"Trend rate: ultimate", 9, 3, 1)
Call insertField(errorArray, cellArray, _
"Annual decrease", 10, 3, 1)
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook.Name)
End Function
Public Function pasteFAS106LiabCells_1_10(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String, ByRef processingWithSubsidy As Boolean, ByRef
processingWithoutSubsidy As Boolean, ByVal radioFAS106WithSubsidytotal
As Boolean, ByVal radioFAS106WithoutSubsidytotal As Boolean)
Dim maxColumn As Integer
Dim row As Integer, reportBreak As Integer
Dim startColumn As Integer, endColumn As Integer
Dim Worksheetname As String
reportBreak = 1
maxColumn = UBound(cellArray, 2)
If maxColumn > 5 And ((processingWithSubsidy And Not
radioFAS106WithSubsidytotal) Or (processingWithoutSubsidy And Not
radioFAS106WithoutSubsidytotal)) Then
Call addWarningMessage(errorArray, "WorkbookToUpdate 2.0 can
handle a maximum of 3 liability groups. Your integration workbook
contains " + Trim(maxColumn - 2) + "." +
WorkbookToUpdateWorkbook.Name)
Exit Function
End If
If (processingWithSubsidy = True) Then
If (radioFAS106WithSubsidytotal = True) Then
startColumn = 1
endColumn = 1
ElseIf (radioFAS106WithSubsidytotal = False) Then
startColumn = 2
endColumn = maxColumn - 1
If (startColumn > endColumn) Then
Call addWarningMessage(errorArray, "Error: Import
using Report Breaks selected for FAS106 With Subsidy, but no Report
Breaks exist in " + importInfo.sIntegration_workBookName)
End If
End If
End If
If (processingWithoutSubsidy = True) Then
If (radioFAS106WithoutSubsidytotal = True) Then
startColumn = 1
endColumn = 1
ElseIf (radioFAS106WithoutSubsidytotal = False) Then
startColumn = 2
endColumn = maxColumn - 1
If (startColumn > endColumn) Then
Call addWarningMessage(errorArray, "Error: Import
using Report Breaks selected for FAS106 Without Subsidy, but no Report
Breaks exist in " + importInfo.sIntegration_workBookName)
End If
End If
End If
Do While (startColumn >= 1) And (startColumn <= endColumn)
Worksheetname = "FAS" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID)) + "-
liab" + CStr(reportBreak)
If Not (canActivateWorksheet(Worksheetname)) Then
GoTo ActivateFailed
Else

WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
Application.StatusBar = "Updating " + Worksheetname
End If
Call insertField(errorArray, cellArray, _
"Employer APBO Total", 35, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Service cost", 36, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Expected benefit payments for year", 37, 4,
startColumn)
GoTo NextColumn
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname +
" does not exist in " + WorkbookToUpdateWorkbook.Name)
NextColumn:
startColumn = startColumn + 1
reportBreak = reportBreak + 1
Loop
End Function
Public Function pasteERISACells_1_10(ByRef importInfo As ImportParams,
ByRef cellArray() As String, ByRef errorArray() As String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "USOld" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Market value of assets", 58, 5, 1)
Call insertField(errorArray, cellArray, _
"Actuarial value of assets", 59, 5, 1)
Call insertField(errorArray, cellArray, _
"Credit Balance/(Deficiency)", 62, 5, 1)
Call insertField(errorArray, cellArray, _
"Accumulated Reconciliation Account", 63, 5, 1)
Call insertField(errorArray, cellArray, _
"Unfunded Old Liability", 98, 5, 1)
Call insertField(errorArray, cellArray, _
"Unfunded Old Liability Amount", 99, 5, 1)
Dim startRow As Integer, fsaImportRow As Integer
row = 0
startRow = 0
fsaImportRow = 66 'offset for the first FSA base
Do While (row <> -1) 'loop to handle multiple fsa bases
row = insertField(errorArray, cellArray, _
"Unamortized Amount", fsaImportRow, 6, 1, startRow)
row = insertField(errorArray, cellArray, _
"Years Remaining", fsaImportRow, 5, 1, startRow)
fsaImportRow = fsaImportRow + 1
startRow = row + 1
Loop
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook.Name)
End Function
Public Function pasteERISACells_2_00(ByRef importInfo As ImportParams,
ByRef cellArray() As String, ByRef errorArray() As String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "USPPA" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Market value of assets", 102, 5, 1)
Call insertField(errorArray, cellArray, _
"Actuarial value of assets", 103, 5, 1)
Call insertField(errorArray, cellArray, _
"Present Value of Contributions receivable", 104, 5, 1)
Call insertField(errorArray, cellArray, _
"Funding Standard Carryover Balance", 105, 5, 1)
Call insertField(errorArray, cellArray, _
"Prefunding Balance", 106, 5, 1)
Call insertField(errorArray, cellArray, _
"Reflect Phase-In Provisions for Applicable Percentages?",
107, 5, 1)
Call insertField(errorArray, cellArray, _
"Prior Year Minimum Contribution", 111, 5, 1)
Call insertField(errorArray, cellArray, _
"Funded Ratio for Quarterly Contributions and At-Risk
status", 112, 5, 1)
Call insertField(errorArray, cellArray, _
"Funded Ratio for Eligibility to Apply Balances", 113, 5,
1)
Call insertField(errorArray, cellArray, _
"Prior Year AVA", 114, 5, 1)
Call insertField(errorArray, cellArray, _
"Prior Year Ongoing Liability", 115, 5, 1)
Call insertField(errorArray, cellArray, _
"Prior Year Prefunding Balance", 116, 5, 1)
Call insertField(errorArray, cellArray, _
"Eligibility for transition percentages for exemption from
new SAB", 117, 5, 1)
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook.Name)
End Function
Public Function pasteERISAInputCells_1_10(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "Input" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Interest rate", 33, 3, 1)
Call insertField(errorArray, cellArray, _
"Compensation Increase - male", 34, 3, 1)
Call insertField(errorArray, cellArray, _
"Crediting Rate", 35, 3, 1)
Call insertField(errorArray, cellArray, _
"Current liability rate for min", 37, 3, 1)
Call insertField(errorArray, cellArray, _
"Top of CL range for min", 38, 3, 1)
'Call insertField(errorArray, cellArray, _
"Current liability rate for max", 19, 3, 1)
Call insertField(errorArray, cellArray, _
"PBGC required interest rate", 40, 3, 1)
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook.Name)
End Function
Public Function pasteERISAInputCells_2_00(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String)
Dim row As Integer
Dim Worksheetname As String
Worksheetname = "Input" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID))
On Error GoTo ActivateFailed
WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
On Error GoTo 0
Application.StatusBar = "Updating " + Worksheetname
Call insertField(errorArray, cellArray, _
"Effective Interest Rate", 33, 3, 1)
' Call insertField(errorArray, cellArray, _
' "Year 1 Discount Rate", 33, 4, 1)
' Call insertField(errorArray, cellArray, _
' "Year 2 Discount Rate", 33, 5, 1)
' Call insertField(errorArray, cellArray, _
' "Year 3 Discount Rate", 33, 6, 1)
' Call insertField(errorArray, cellArray, _
' "Year 4 Discount Rate", 33, 7, 1)
' Call insertField(errorArray, cellArray, _
' "Year 5 Discount Rate", 33, 8, 1)
' Call insertField(errorArray, cellArray, _
' "Year 6 Discount Rate", 33, 9, 1)
' Call insertField(errorArray, cellArray, _
' "Year 7 Discount Rate", 33, 10, 1)
Call insertField(errorArray, cellArray, _
"Compensation Increase - male", 34, 3, 1)
Call insertField(errorArray, cellArray, _
"Crediting Rate", 35, 3, 1)
Exit Function
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname + "
does not exist in " + WorkbookToUpdateWorkbook.Name)
End Function
Public Function pasteERISALiabCells_1_10(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String, ByVal radioERISAtotal As Boolean)
Dim maxColumn As Integer
Dim row As Integer, reportBreak As Integer
Dim startColumn As Integer, endColumn As Integer
Dim Worksheetname As String
reportBreak = 1
maxColumn = UBound(cellArray, 2)
If maxColumn > 5 And Not radioERISAtotal Then
Call addWarningMessage(errorArray, "WorkbookToUpdate 2.0 can
handle a maximum of 3 liability groups. Your integration workbook
contains " + Trim(maxColumn - 2) + "." +
WorkbookToUpdateWorkbook.Name)
Exit Function
End If
If (radioERISAtotal = True) Then
startColumn = 1
endColumn = 1
Else
startColumn = 2
endColumn = maxColumn - 1
If (startColumn > endColumn) Then
Call addWarningMessage(errorArray, "Error: Import using
Report Breaks selected for ERISA, but no Report Breaks exist in " +
importInfo.sIntegration_workBookName)
End If
End If
Do While startColumn <= endColumn
Worksheetname = "USOld" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID)) + "-
liab" + CStr(reportBreak)
'check to see if the liab worksheet exists
If Not (canActivateWorksheet(Worksheetname)) Then
GoTo ActivateFailed
Else

WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
Application.StatusBar = "Updating " + Worksheetname
End If
Dim retVal As Integer
Call insertField(errorArray, cellArray, _
"AAL - Total", 44, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Normal Cost - AAL", 45, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Present Value of Benefits - Total", 46, 4,
startColumn)
Call insertField(errorArray, cellArray, _
"Temporary Annuity Factor (TAF)", 47, 4, startColumn)
Call insertField(errorArray, cellArray, _
"RPA CL - Total", 48, 4, startColumn)
Call insertField(errorArray, cellArray, _
"RPA '94 Current Liability Normal Cost", 49, 4,
startColumn)
Call insertField(errorArray, cellArray, _
"Top-of-range CL for min - Total", 50, 4, startColumn)
'Call insertField(errorArray, cellArray, _
"Current Liability for Max - Total", 51, 4,
startColumn)
'Call insertField(errorArray, cellArray, _
"Current Liability Normal Cost for max", 33, 4,
startColumn)
'If insertField(errorArray, cellArray, "Expected Benefit
Payments", 34, 4, startColumn, , False) = -1 Then
Call insertField(errorArray, cellArray, _
"Expected Benefit Payments - AAL, OBRA '87", 51,
4, startColumn)
'End If
Call insertField(errorArray, cellArray, _
"Expected Benefit Payments - RPA '94", 52, 4,
startColumn)
Call insertField(errorArray, cellArray, _
"PBGC - Total", 53, 4, startColumn)
GoTo NextColumn
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname +
" does not exist in " + WorkbookToUpdateWorkbook.Name)
NextColumn:
startColumn = startColumn + 1
reportBreak = reportBreak + 1
Loop
End Function
Public Function pasteERISALiabCells_2_00(ByRef importInfo As
ImportParams, ByRef cellArray() As String, ByRef errorArray() As
String, ByVal radioERISAtotal As Boolean)
Dim maxColumn As Integer
Dim row As Integer, reportBreak As Integer
Dim startColumn As Integer, endColumn As Integer
Dim Worksheetname As String
reportBreak = 1
maxColumn = UBound(cellArray, 2)
If maxColumn > 5 And Not radioERISAtotal Then
Call addWarningMessage(errorArray, "WorkbookToUpdate 2.0 can
handle a maximum of 3 liability groups. Your integration workbook
contains " + Trim(maxColumn - 2) + "." +
WorkbookToUpdateWorkbook.Name)
Exit Function
End If
If (radioERISAtotal = True) Then
startColumn = 1
endColumn = 1
Else
startColumn = 2
endColumn = maxColumn - 1
If (startColumn > endColumn) Then
Call addWarningMessage(errorArray, "Error: Import using
Report Breaks selected for ERISA, but no Report Breaks exist in " +
importInfo.sIntegration_workBookName)
End If
End If
Do While startColumn <= endColumn
Worksheetname = "USPPA" +
Trim(getPlanInfoFromIndex(SelectedPlanName, INDEXCOL_TEMPLATEID)) + "-
liab" + CStr(reportBreak)
'check to see if the liab worksheet exists
If Not (canActivateWorksheet(Worksheetname)) Then
GoTo ActivateFailed
Else

WorkbookToUpdateWorkbook.Worksheets(Worksheetname).Activate
Application.StatusBar = "Updating " + Worksheetname
End If
Dim retVal As Integer
Call insertField(errorArray, cellArray, _
"Target - Total", 46, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Target Normal Cost", 47, 4, startColumn)
Call insertField(errorArray, cellArray, _
"At-Risk - Total", 48, 4, startColumn)
Call insertField(errorArray, cellArray, _
"At-Risk Normal Cost", 49, 4, startColumn)
Call insertField(errorArray, cellArray, _
"PUC Funding Target - Total", 50, 4, startColumn)
Call insertField(errorArray, cellArray, _
"At-Risk PBGC - Total", 52, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Ongoing PBGC - Total", 51, 4, startColumn)
Call insertField(errorArray, cellArray, _
"Expected Benefit Payments", 53, 4, startColumn)
GoTo NextColumn
ActivateFailed:
Call addWarningMessage(errorArray, "Error: " + Worksheetname +
" does not exist in " + WorkbookToUpdateWorkbook.Name)
NextColumn:
startColumn = startColumn + 1
reportBreak = reportBreak + 1
Loop
End Function
Public Function getPlanInfoFromIndex(ByVal sPlanName As String, ByVal
iCol As Integer) As Long
Dim IndexTable As Range, row As Range
Set IndexTable = ThisWorkbook.Names("IndexTable").RefersToRange
For Each row In IndexTable.Rows
If row.Cells(1, 1).Value = "" Then
Exit Function
End If
If UCase(row.Cells(1, 1).Value) = UCase(sPlanName) Then
getPlanInfoFromIndex = cvLng(row.Cells(1, iCol).Value)
End If
Next row
End Function

 

[Peter T]

OK so you are sure the project t.b. updated is not calling the code in
another project to do the update. Are you really sure, reason for asking is
I'm surprised it was not already 100% known for sure without having to go
and check, as it appears you had to do.

I'm afraid scan reading the code below does not give any clues. The updated
code in the adjacent post is 8-900 lines many of which are wrapped. It would
take a long time to reconstruct. And probably much longer to get a feel for
what it all does (out of context)

Some things to try -

Paste what you think is the correct code into the module in the project t.b.
updated.

Update a module in a dummy project (just the suspect module), using code to
do the update, is the new code as it should be.

Working with the actual project t.b. updated, put a break in the code after
it has done the 95% that works fine, then step through the rest with F8.

If the update code is "totally in a separate project, and not called from
the project t.b. updated, I sort of suspect the problem will relate to
something obvious. Just need to see it in the wood amongst the trees.

Regards,
Peter T


I was thinking along the same lines as far as where the call to update
the code initiated. And to ensure its independence, I created a menu
item that calls the Updater add-in when clicked. The Add-In is given
a reference to the workbook to updated when the workbook to update is
opened. So... the code that is being executed is not contained within
the updating workbook at all. Here's my updating code:
<snip>