Looking at this Stack Overflow question which references this MSDN Doc, it appears that I can't pass a parameter being sent into the calling Sub int a Lambda expression because it is a ByRef parameter.
I have created a temp parameter (two actually - there are two that are required in the Lambda expression) and passed that into the long-running Task.
The task executes very well. It uses a Parallel.ForEach expression which runs at 30% of the original time.
The issue is that both of these ByRef parameters are needed below this call.
The Parallel.ForEach inserts hundreds of values into Excel. The statements below should do formatting and also enable each cell to be clickable to open a dialog window with additional information. Neither the formatting or clickability work.
Here is the calling Sub signature, the Lambda expression, and some code that gets called afterward:
Friend Sub RefreshFormattingSummaryReport(ByRef oDPWorksheet As CDPWorksheet, ByRef oSheet As Microsoft.Office.Interop.Excel.Worksheet,
ByRef worker As BackgroundWorker)
Dim tempDPWorksheet As New CDPWorksheet
tempDPWorksheet = oDPWorksheet
Dim tempOsheet As New Microsoft.Office.Interop.Excel.Worksheet
tempOsheet = oSheet
Parallel.ForEach(rangeList,
Sub(singleTuple)
Dim iRow1 As Integer = 0
Dim iRow2 As Integer = 0
Dim iMaxRow As Integer = 0
Dim iCol1 As Integer = 0
Dim iCol2 As Integer = 0
Dim oRange As Range
Dim oRange2 As Range
Dim oCellValues() As String = Nothing
Dim oDesignSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim bLoadFormulas As Boolean = False
Dim NamedRange = singleTuple.NamedRange
Dim NamedRangeRow = singleTuple.NamedRangeRow
Dim NamedRangeColumn = singleTuple.NamedRangeColumn
Dim NamedRangeCellValue = singleTuple.NamedRangeCellValue
If InStr(NamedRange, tempDPWorksheet.SheetID) > 0 Then
iRow1 = Integer.Parse(NamedRangeRow.ToString.Substring(0, IIf(InStr(NamedRangeRow, "-") > 0, InStr(NamedRangeRow, "-") - 1, Len(NamedRangeRow))))
iRow2 = Integer.Parse(NamedRangeRow.ToString.Substring(IIf(InStr(NamedRangeRow, "-") > 0, InStr(NamedRangeRow, "-"), 0), IIf(InStr(NamedRangeRow, "-") > 0, Len(NamedRangeRow) - InStr(NamedRangeRow, "-"), Len(NamedRangeRow))))
If iRow2 > iMaxRow Then iMaxRow = iRow2
If InStr(NamedRangeColumn, "DP_COL") > 0 Then
oRange = tempOsheet.Range(NamedRangeColumn)
iCol1 = oRange.Column
iCol2 = iCol1
oRange = Nothing
Else
iCol1 = Integer.Parse(NamedRangeColumn.ToString.Substring(0, IIf(InStr(NamedRangeColumn, "-") > 0, InStr(NamedRangeColumn, "-") - 1, Len(NamedRangeColumn))))
iCol2 = Integer.Parse(NamedRangeColumn.ToString.Substring(IIf(InStr(NamedRangeColumn, "-") > 0, InStr(NamedRangeColumn, "-"), 0), IIf(InStr(NamedRangeColumn, "-") > 0, Len(NamedRangeColumn) - InStr(NamedRangeColumn, "-"), Len(NamedRangeColumn))))
End If
oRange = CType(tempOsheet.Range(tempOsheet.Cells(iRow1, iCol1), tempOsheet.Cells(iRow2, iCol2)), Microsoft.Office.Interop.Excel.Range)
oCellValues = Split(Replace(Replace(Replace(Replace(Replace(Replace(NamedRangeCellValue, "{", ""), "}", ""), "&", "&"), "amp;", ""), "<", "<"), ">", ">"), ",| ")
If oRange IsNot Nothing Then
If oCellValues.Length = oRange.Rows.Count Then
For j = 1 To oRange.Rows.Count
Try
oRange2 = oRange.Cells.Item(j)
If oCellValues(j - 1).Length >= 2 Then
oRange2.Value = IIf(oCellValues(j - 1).Substring(0, 2).Equals(" "), oCellValues(j - 1), LTrim(oCellValues(j - 1)))
Else
oRange2.Value = oCellValues(j - 1)
End If
Catch ex As Exception
'MessageBox.Show(String.Format("Error: {0}", ex.Message, oRange.Cells.Item(j)))
End Try
Next
End If
End If
tempOsheet.Names.Add(Replace(NamedRange, tempDPWorksheet.SheetID & ".", "").Replace(" ", "_").Replace("-", "_").Replace(",", ""), oRange)
Try
If tempDPWorksheet.Columns.Contains(NamedRangeColumn) Then
bLoadFormulas = (tempDPWorksheet.Columns.IndexOf(NamedRangeColumn) <> -1) AndAlso (DirectCast(tempDPWorksheet.Columns(tempDPWorksheet.Columns.IndexOf(NamedRangeColumn)), CColumn).Type = EColumnTypes.Excel) Or NameIsExcelRow(NamedRange.ToString)
Else
bLoadFormulas = False
End If
If Not (InStr(NamedRange, "DP_FRO") > 0 Or InStr(NamedRange, "DRILLPOINT") > 0) Then
' ************** Repetitive Copy/Paste operations
RangeCopyDesignFormat(oRange, oDesignSheet.Range(Replace(NamedRange, tempDPWorksheet.SheetID & ".", "")), False, Not tempDPWorksheet.IncludeFormatting Or Not (Not (InStr(NamedRange, "SKIP") > 0) Or tempDPWorksheet.FormatSkipped), False, "", bLoadformulas:=bLoadFormulas, bLoadComments:=True)
End If
Catch ex As Exception
End Try
End If
End Sub)
And some calls that are made afterward:
oSheet = tempOsheet
oDPWorksheet = tempDPWorksheet
ReplaceKeywordsInHeaderAndFooter("", oDBInfo, oSheet, "", "", oProp.Value)
If oDPWorksheet.IncludeDrilldown Then
DrillDownLoad(oSheet, oDPWorksheet, oSmartTags, oDBInfo, "", 4 + iRowOffset)
End If
SuppressRows(oSheet, oDPWorksheet, oDBInfo, iMaxRow, iRowOffset:=iRowOffset)
If Not oDPWorksheet.DoNotAutofitColumns Then
tWidth = oSheet.Range("DP_FRO_" & oDPWorksheet.FROutlineID).ColumnWidth
oSheet.Range("DRILLPOINT").Columns.AutoFit()
oSheet.Range("DP_FRO_" & oDPWorksheet.FROutlineID).ColumnWidth = tWidth
End If
HideColumns(oSheet, oDPWorksheet.Columns, "DP_FRO_" & oDPWorksheet.FROutlineID, oDPWorksheet.HideFROColumn)
I have tried assigning the temp variables back to the ByRef parameters, and I have also tried changing each instance of the ByRef parameters in the rest of the Sub with the temp names. Neither work.
Any thoughts? Any suggestions are appreciated.
