-
Nov 10th, 2020, 02:31 AM
#1
Thread Starter
Banned
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
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.
-
Nov 10th, 2020, 03:48 AM
#2
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.
-
Nov 12th, 2020, 03:48 AM
#3
Thread Starter
Banned
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
-
Nov 12th, 2020, 04:15 AM
#4
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.
-
Nov 12th, 2020, 12:02 PM
#5
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
-
Nov 17th, 2020, 04:54 AM
#6
Thread Starter
Banned
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
-
Nov 17th, 2020, 12:49 PM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|