Results 1 to 7 of 7

Thread: Function Works in Excel Macro But Does not Work in VB.Net

  1. #1

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Function Works in Excel Macro But Does not Work in VB.Net

    Dear All

    I have an Industrial code with function which works in VBA and I am trying to convert it to VB.net as per our management decision.

    Here is the Code and Function Code which are working in VBA

    Code
    Code:
    Sub CutEm()
        Dim rOut As Range
        Dim avInp As Variant
        Dim dKerf As Double
        Dim dStk As Double
        Dim iOut As Long
        Dim nOut As Long
        dKerf = Range("ptrKerf").Value2
        dStk = Range("ptrStk").Value2
        With Range("rgnInp")
            If WorksheetFunction.Max(.Columns(1).Value) > dStk Then
                MsgBox "Piece length cannot exceed stock length", _
                       Buttons:=vbOKOnly, _
                       Title:="shg Cut List"
                Exit Sub
            End If
            avInp = .Value
            iOut = .Column + 3
            Set rOut = .Worksheet.Cells(.Row, iOut).Resize(.Rows.count)
        End With
        rOut.Resize(, Columns.count - iOut + 1).EntireColumn.Clear
        rOut(1, 0).Resize(Rows.count - rOut.Row).ClearContents
        Application.ScreenUpdating = False
        **With WorksheetFunction
            Do While .Sum(.Index(avInp, 0, 2))
                nOut = nOut + 1
                rOut.Columns(nOut) = .Transpose(aiCut(avInp, dKerf, dStk)) 'Function
            Loop
        End With**
        Set rOut = rOut.Resize(, nOut)
        With rOut
            .Style = "Input"
            .NumberFormat = "General_);;"
                 With .Rows(-1)
                .FormulaR1C1 = "=column() - " & .Column - 1
                .Value = .Value
                .Style = "Input"
            End With
            With .Rows(0)
           .FormulaR1C1 = "=max(0, ptrStk - sumproduct(r[1]c:r[" & 
           rOut.Rows.count & "]c, rgnLen + ptrKerf))"
                .Style = "Formula"
                .NumberFormat = "0.0"
            End With
            .Columns(0).FormulaR1C1 = "=sum(rc[1]:rc[" & nOut & "])"
            .EntireColumn.AutoFit
            .Worksheet.PageSetup.PrintArea = .Address
         End With
    Here is the Function:

    Code:
    Function aiCut(avInp As Variant, dKerf As Double, dStk As Double) As Long()
            Dim col As New Collection
            Dim nInp, iInp, aiQty(), aiMin() As Long
            Dim dRem, dRemMin As Double
            nInp = UBound(avInp)
            dRemMin = dStk
            ReDim aiQty(1 To nInp)    
            With col
                iInp = 1
                dRem = dStk
                Do
                    Do While avInp(iInp, 1) > dRem Or avInp(iInp, 2) <= 0
                        iInp = iInp + 1
                        If iInp > nInp Then
                            If dRem < dRemMin Then
                                dRemMin = dRem
                                aiMin = aiQty
                                If dRemMin < 0.001 * dStk Then GoTo Outtahere
                            End If
                            If .Count = 1 Then
                                GoTo Outtahere
                            Else
                                iInp = .Item(.Count)
                                If iInp >= nInp Then
                                    GoTo Outtahere
                                Else
                                    dRem = dRem + avInp(iInp, 1) + dKerf
                                    avInp(iInp, 2) = avInp(iInp, 2) + 1
                                    aiQty(iInp) = aiQty(iInp) - 1
                                    iInp = iInp + 1
                                    .Remove(.Count)
                                End If
                            End If
                        End If
                    Loop
                    .Add(Item:=iInp)
                    avInp(iInp, 2) = avInp(iInp, 2) - 1
                    aiQty(iInp) = aiQty(iInp) + 1
                    dRem = dRem - avInp(iInp, 1) - dKerf
                Loop
            End With
    Outtahere:
            aiCut = aiMin   
            For iInp = 1 To nInp
                avInp(iInp, 2) = avInp(iInp, 2) + aiQty(iInp) - aiMin(iInp)
            Next iInp
        End Function
    VB.net problems:
    1- Variant Data type has no equivalent except Object which sometimes does not work as Variant.
    2- Syntax
    Code:
    Redim aiQty(1 to nInp)
    does not accept 1 but 0 because of
    Code:
    UBound(avInp)
    in the Function.

    Hope that the question is clear and appreciate your help

    Thanks, Regards
    Last edited by meho2020; Nov 12th, 2020 at 03:46 AM.

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Function Works in Excel Macro But Does not Work in VB.Net

    why don't you just explain what you want to do with the sheet, rather than people try to figure out what your VBA code
    is doing
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Function Works in Excel Macro But Does not Work in VB.Net

    Dear ChrisE
    I edited the question above with full details, Hope it is clear now, Thanks

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Function Works in Excel Macro But Does not Work in VB.Net

    here some basics for working with Excel and .NET

    Code:
    Option Strict On
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
    
            Try
                Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
                Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
                xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
                Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle1"), Worksheet)
                Dim ColRange = xlSt.UsedRange.Columns.Count 'count the used Columns      
                Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows
    
                'show UsedRanges
                MessageBox.Show(CStr(ColRange))
                MessageBox.Show(CStr(rowRange))
    
                With xlSt
                    'do stuff here with Sheet
                    .Range("D7").Value = "56"
                End With
    
                xlApp.Save()
                xlApp.Quit()
                xlApp = Nothing
            Catch g As Exception
                MessageBox.Show(g.ToString)
            End Try
        End Sub
    
       
    
    
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Try
                Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
                Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
                xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
                Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle3"), Worksheet)     
                Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows
           
    
                'Dim rowRange = Worksheet.UsedRange.Rows.Count 'how many rows are used
                Dim mySum = rowRange + 1 'add Formula at the end so =  + 1
    
    
                With xlSt
    
                    '1.) sample
                    'Sum Column B
    'Note I'm in Germany so I use Summe
                    .Range("B" & mySum).FormulaLocal = "=Summe(B2:B" & rowRange & ")"
    
                    '2.) sample
                    'Sum the rows and the result to Column G
                    'copy formula to range
                    '.Range("G2").FormulaLocal = "=SUMME(A2:F2)"
                    '.Range("G2").Copy()
                    '.Range("G3:G" & rowRange).PasteSpecial(XlPasteType.xlPasteFormulas)
    
                End With
                xlWb.Save()
                xlApp.Quit()
                xlApp = Nothing
            Catch g As Exception
                MessageBox.Show(g.ToString)
            End Try
        End Sub
    End Class
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Function Works in Excel Macro But Does not Work in VB.Net

    Variant doesn't have a true equivalent, but it usually doesn't matter. After all, most data is not truly "anything we feel like", but some subset of that. That seems to hold true for the only Variant I see in your code. That clearly isn't just "whatever", so what IS that? Make it the type that it needs to be. At worst, it's a string, or possibly a custom object.

    You're right about the arrays. In VBA, arrays could start anywhere and go anywhere. In VB.NET, all arrays start at 0. This may require a shift in thinking about it, but it ultimately makes little difference. All arrays already have a range of indexes, all that .NET is doing is shifting all indexes down to 0. Doing so mathematically isn't particularly difficult, as all you are really doing is subtracting the low bounds of the VBA array.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: Function Works in Excel Macro But Does not Work in VB.Net

    Dear All

    I Changed Variant Type to Object Type and I receive Error "Index was outside the bounds of the Array at the Code Line

    Code:
    dRem = dRem + avInp(iInp, 1) + dKerf
    Thanks

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Function Works in Excel Macro But Does not Work in VB.Net

    OK, so you did. Now the question is why? The compiler isn't lying, and the message can only have one meaning: iInp is too big. I'd say that you have a bounds issue, since you do check whether iInp> nInp, but that's only the proximal issue, and while solving it would kind of help, it would also kind of hurt. The simplest fix to that would be to subtract 1 from nInp, which would do no more than change where the exception occurs, because it doesn't truly fix the problem.

    The ultimate solution is to learn a bit of debugging, because that's the real key to this problem and several others. Put a breakpoint on the line that has the exception. To set a breakpoint, F9 is the easiest until you have set your first breakpoint. Once you have, you'll see a red dot in the left margin. From then on, the easiest way to set a breakpoint is to click in the left margin beside the place you want to set the breakpoint. You can remove them by clicking on the red dot, as well, among other means.

    Once you have a breakpoint, run the program and take a look at what is in nInp. It isn't what you are expecting it to be. You can look at any variable usually by hovering the mouse over it, though sometimes you have to highlight it and press Shift+F9. Shift+F9 can also be used to evaluate expressions, and to give you more screen to study a complex object with greater leisure.

    You'll also want to know about F10, F11 (two different means to step forwards through the code, and you can figure out the difference between them) and F5 (resume).

    The key to this particular issue is that the Do While condition isn't great, but that gets into a different issue. In VBA, you would have had Or and And, which still exist in .NET, buy you also have OrElse and AndAlso, which is what you want to be using in 99% of all cases. What they do may not make sense right away, but they basically return as soon as the truth of the whole conditional can be known. For example:

    If A AndAlso B Then

    If A is false, then it really doesn't matter what B is, so there's no reason to look. If you use And, then both A and B will be evaluated, but if you use AndAlso, then if A is false, B never gets evaluated. In your case, you need to add a clause to the Do While to check that iInp isn't too large. You need to do this before either of the other two clauses you currently have (so it has to be first), and you need to be using OrElse or AndAlso (you can write the whole thing using either one, so it's your choice) such that if iInp IS too large, then the other two clauses don't get evaluated.

    There are several ways this could be done, but the key point is the debugging. Seeing what is happening is FAR better than just looking at the code and guessing (though, technically, that's kind of what I'm doing with this answer).

    After that, you might consider turning Option Strict ON, which will probably reveal a whole lot of other issues that should be fixed. After all, avInp clearly shouldn't be either Variant OR Object. It MUST be a 2D array of numbers, so it should be either a 2D array of integer, decimal, or double, depending on what the numbers are.

    There are also some type issues that barely matter. For example, Long should be changed into Integer. In VBA, Long was a 32 bit integer. In .NET, Integer is a 32 bit integer and Long is a 64 bit integer. Long are every so slightly slower to work with than Integer. It doesn't really matter, but it's good practice.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width