Converting Code from VBA to VB.NET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have code that works in VBA that I must convert to VB.Net. It works
perfectly behind an Excel Form.

I am writing a program in VB.Net that fills an Excel Document. I've
exhausted my help in the Excel Programming Forum.

I was hoping there are some ideas here.

This is the VBA Code:
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single

If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub


I changed the code and now get an error on this line of code (full code
below): For Each CurrCell In CoverWs.Range(strA1).MergeArea

Error:
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in mscorlib.dll

Additional information: Member not found.


Current Code:
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Excel.Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single

If CoverWs.Range(strA1).MergeCells Then
With CoverWs.Range(strA1).MergeArea
If .Rows.Count = 1 And .WrapText = True Then
ThisApplication.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = CoverWs.Range(strA1).ColumnWidth
For Each CurrCell In
CoverWs.Range(strA1).MergeArea
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit()
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight >
PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If



Any advice appreciated!

Thansk in advance,
Elena
 
You don't show the definition and initialisation of strA1 in your
revised code. Maybe you just need to add the line:

strA1 = "A1"
 
It is in a loop. I apologize; I should have included the whole loop. It has
to be a string because the row is always changing. I hope this is clearer.
The code is below.

Thanks again,
Elena


If intCount1 > 0 Then
While intRow1 <> intCount1
Dim strRec =
dsRec.Tables(0).Rows(intRow1)("recommendations_content").ToString
Dim strA1 As String = "a" & intPrintRow
Dim strJ1 As String = "j" & intPrintRow
CoverWs.Range(strA1 & ":" & strJ1).Merge()
CoverWs.Range(strA1).Value = strRec
CoverWs.Range(strA1).Font.Bold = False
CoverWs.Range(strA1).WrapText = True

Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Excel.Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single

If CoverWs.Range(strA1).MergeCells Then
With CoverWs.Range(strA1).MergeArea
If .Rows.Count = 1 And .WrapText = True Then
ThisApplication.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = CoverWs.Range(strA1).ColumnWidth
For Each CurrCell In
CoverWs.Range(strA1).MergeArea()
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit()
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight >
PossNewRowHeight, CurrentRowHeight, PossNewRowHeight)
End If
End With
End If

intPrintRow = intPrintRow + 1

intRow1 = intRow1 + 1



End While
 
Back
Top