Home All Groups Group Topic Archive Search About

Converting Code from VBA to VB.NET

microsoft.public.dotnet.languages.vb
Author
2 Jun 2006 2:36 PM
Elena
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

Author
2 Jun 2006 4:08 PM
Andrew Taylor
You don't show the definition and initialisation of strA1 in your
revised code. Maybe you just need to add the line:

  strA1 = "A1"


Elena wrote:
Show quoteHide quote
> 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
Author
2 Jun 2006 4:20 PM
Elena
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



Show quoteHide quote
"Andrew Taylor" wrote:

> You don't show the definition and initialisation of strA1 in your
> revised code. Maybe you just need to add the line:
>
>   strA1 = "A1"
>
>
> Elena wrote:
> > 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
>
>



Post Other interesting topics
Calling VB.Net classes from VB6
Data base insert Error HELP!
Adding a row to a table help!
Is there a Forms collection?
How to measure bandwidth need by the application
Why doesn't changing the position in a table change the position of the DatGridView that's bound to
UI to enter values into a structure
Crypto removing white spaces from byte array
Is RegEx a good choice for reading malformed xml?
How to expire application