[RESOLVED] Need help please Vba macro to dll for Excel
Hi I am brand new to VB so a total noobie here I want to call the macro in excell from the dll I want to register. Gave me errors. The macro name is getcombos -I am using Visual Basic 2005
This is the code I tried -gave me errors ?????? Any help appreciated as I said total new to VB.
Code:
Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
' the following is an Attribute spcifying that the class can be accesses in a unmanaged (non-.net) way
Namespace ExcelExample
Public Class Class1
Public Sub Getcombos() As Integer
Dim appInst As New Microsoft.Office.Interop.Excel.Application
a = appInst.Sheets("Nrs").name
Dim rngNumbers As Range
Dim i As Long, j As Long, k As Long
Dim colResults As New Collection
Dim arrResults() As String
Dim arrComboLoc As Object
Dim LocIndex As Long
Dim dTot As Double
Dim str As String
Dim dTargetSum As Double
Dim bAdvanced As Boolean
Sheets("Nrs").Select()
Call Clean1()
rngNumbers = Range("B2", Cells(Rows.Count, "B").End(xlUp))
Range("E2:E" & Rows.Count).ClearContents()
If Not IsNumeric(Range("D2").Value) _
Or Len(Trim(Range("D2").Value)) = 0 Then
Range("D2").Select()
MsgBox("Must provide a Target SUM number")
Exit Sub
End If
If Not IsNumeric(Range("D3").Value) _
Or Len(Trim(Range("D3").Value)) = 0 Then
Range("D3").Select()
MsgBox("Must provide the number of cells to use")
Exit Sub
ElseIf Range("D3").Value > rngNumbers.Cells.Count Then
Range("D3").Select()
MsgBox("Number of cells may not exceed total amount of cells")
Exit Sub
ElseIf Range("D3").Value < 1 Then
Range("D3").Select()
MsgBox("Number of cells may not be less than 1")
Exit Sub
End If
dTargetSum = Range("D2").Value
arrComboLoc = Application.Transpose(Evaluate("Index(Row(1:" & Range("D3").Value & "),)"))
On Error Resume Next
For i = 1 To WorksheetFunction.Combin(rngNumbers.Count, Range("D3").Value)
dTot = 0
str = vbNullString
For LocIndex = LBound(arrComboLoc) To UBound(arrComboLoc)
dTot = dTot + rngNumbers.Cells(arrComboLoc(LocIndex)).Value
str = str & ", " & rngNumbers.Cells(arrComboLoc(LocIndex)).Value
Next LocIndex
If dTot = dTargetSum Then
str = Mid(str, 3)
colResults.Add(str, str)
End If
bAdvanced = False
For j = UBound(arrComboLoc) To LBound(arrComboLoc) Step -1
If arrComboLoc(j) < rngNumbers.Cells.Count - (UBound(arrComboLoc) - j) Then
arrComboLoc(j) = arrComboLoc(j) + 1
For k = j + 1 To UBound(arrComboLoc)
arrComboLoc(k) = arrComboLoc(j) + k - j
Next k
bAdvanced = True
Exit For
End If
If bAdvanced = True Then Exit For
Next j
Next i
If colResults.Count > 0 Then
ReDim Preserve arrResults(1 To colResults.Count)
For i = 1 To colResults.Count
arrResults(i) = colResults(i)
Next i
Range("E2").Resize(colResults.Count).Value = Application.Transpose(arrResults)
Else
'MsgBox "No valid combinations found to be less than or equal to " & dTargetSum & " when using " & Range("D3").Value & " cells."
End If
Call Del_Cleanupchar()
Call DeleteArray1()
Call LeadZ()
Call Split()
Call Convert()
End Sub
End Class
End Namespace
This is the Vba macro
Code:
Sub GetCombos()
Dim rngNumbers As Range
Dim i As Long, j As Long, k As Long
Dim colResults As New Collection
Dim arrResults() As String
Dim arrComboLoc As Variant
Dim LocIndex As Long
Dim dTot As Double
Dim str As String
Dim dTargetSum As Double
Dim bAdvanced As Boolean
Sheets("Nrs").Select
Call Clean1
Set rngNumbers = Range("B2", Cells(Rows.Count, "B").End(xlUp))
Range("E2:E" & Rows.Count).ClearContents
If Not IsNumeric(Range("D2").Value) _
Or Len(Trim(Range("D2").Value)) = 0 Then
Range("D2").Select
MsgBox "Must provide a Target SUM number"
Exit Sub
End If
If Not IsNumeric(Range("D3").Value) _
Or Len(Trim(Range("D3").Value)) = 0 Then
Range("D3").Select
MsgBox "Must provide the number of cells to use"
Exit Sub
ElseIf Range("D3").Value > rngNumbers.Cells.Count Then
Range("D3").Select
MsgBox "Number of cells may not exceed total amount of cells"
Exit Sub
ElseIf Range("D3").Value < 1 Then
Range("D3").Select
MsgBox "Number of cells may not be less than 1"
Exit Sub
End If
dTargetSum = Range("D2").Value
arrComboLoc = Application.Transpose(Evaluate("Index(Row(1:" & Range("D3").Value & "),)"))
On Error Resume Next
For i = 1 To WorksheetFunction.Combin(rngNumbers.Count, Range("D3").Value)
dTot = 0
str = vbNullString
For LocIndex = LBound(arrComboLoc) To UBound(arrComboLoc)
dTot = dTot + rngNumbers.Cells(arrComboLoc(LocIndex)).Value
str = str & ", " & rngNumbers.Cells(arrComboLoc(LocIndex)).Value
Next LocIndex
If dTot = dTargetSum Then
str = Mid(str, 3)
colResults.Add str, str
End If
bAdvanced = False
For j = UBound(arrComboLoc) To LBound(arrComboLoc) Step -1
If arrComboLoc(j) < rngNumbers.Cells.Count - (UBound(arrComboLoc) - j) Then
arrComboLoc(j) = arrComboLoc(j) + 1
For k = j + 1 To UBound(arrComboLoc)
arrComboLoc(k) = arrComboLoc(j) + k - j
Next k
bAdvanced = True
Exit For
End If
If bAdvanced = True Then Exit For
Next j
Next i
If colResults.Count > 0 Then
ReDim Preserve arrResults(1 To colResults.Count)
For i = 1 To colResults.Count
arrResults(i) = colResults(i)
Next i
Range("E2").Resize(colResults.Count).Value = Application.Transpose(arrResults)
Else
'MsgBox "No valid combinations found to be less than or equal to " & dTargetSum & " when using " & Range("D3").Value & " cells."
End If
Call Del_Cleanupchar
Call DeleteArray1
Call LeadZ
Call Split
Call Convert
End Sub
Any help appreciated
Last edited by si_the_geek; Oct 21st, 2017 at 05:24 AM.
Reason: corrected tags
Ah yes, looking at the code I can see that several of those issues are expected.
The VBA editor allows you to write code that is unreliable and not particularly safe, because you can usually "get away with it" without having serious problems. VB.Net forces you to do things in a safer way, which gives higher quality and reliability, but does take a bit more work.
For example, using things like Range("D2").Value is not safe because there is no application or workbook or sheet specified, so Excel needs to guess which of each of them you meant, and sometimes it can get it wrong. .Net doesn't allow you to do things like that, so you can be sure it is working with what you intended (rather than doing something like accidentally writing a value in the wrong workbook).
In terms of this line:
Code:
a = appInst.Sheets("Nrs").name
You haven't declared the variable a , so there is no indication of what data type to use etc, and while it is possible to make .Net guess (like you can with VBA), it isn't a wise move because the guess might cause problems in some cases. Simply changing that line to this should work:
Code:
Dim a as String = appInst.Sheets("Nrs").name
To deal with the issues like Range I mentioned above, create a variable to store the sheet you want to work with. This is untested, but is at least close to what you want:
Code:
Dim NrsSheet as Excel.Worksheet = appInst.Sheets("Nrs")
(ideally a workbook would be specified, but ignore that until everything else is working)
In the rest of the code, anywhere you have Range or Cells or Rows or Columns, put NrsSheet. in front, eg:
Doing these things should eliminate most of the errors you currently have, and you can then post a shorter list of errors that remain (and if you crop the picture down to just the error list, we'll be able to read them much easier)
Ah yes, looking at the code I can see that several of those issues are expected.
The VBA editor allows you to write code that is unreliable and not particularly safe, because you can usually "get away with it" without having serious problems. VB.Net forces you to do things in a safer way, which gives higher quality and reliability, but does take a bit more work.
For example, using things like Range("D2").Value is not safe because there is no application or workbook or sheet specified, so Excel needs to guess which of each of them you meant, and sometimes it can get it wrong. .Net doesn't allow you to do things like that, so you can be sure it is working with what you intended (rather than doing something like accidentally writing a value in the wrong workbook).
In terms of this line:
Code:
a = appInst.Sheets("Nrs").name
You haven't declared the variable a , so there is no indication of what data type to use etc, and while it is possible to make .Net guess (like you can with VBA), it isn't a wise move because the guess might cause problems in some cases. Simply changing that line to this should work:
Code:
Dim a as String = appInst.Sheets("Nrs").name
To deal with the issues like Range I mentioned above, create a variable to store the sheet you want to work with. This is untested, but is at least close to what you want:
Code:
Dim NrsSheet as Excel.Worksheet = appInst.Sheets("Nrs")
(ideally a workbook would be specified, but ignore that until everything else is working)
In the rest of the code, anywhere you have Range or Cells or Rows or Columns, put NrsSheet. in front, eg:
Doing these things should eliminate most of the errors you currently have, and you can then post a shorter list of errors that remain (and if you crop the picture down to just the error list, we'll be able to read them much easier)
Thank you so much for shedding light there - seems the big culprit is the class Public Class Class1 <<<<< -If I remove this line there is only 1 error ??? But I presume the class is necessarily in VB ?
The things I mentioned will still be problems, so I would assume that the "1 error" is a totally catastrophic one that means .Net can't even manage to parse the other code.
You do need the class (or something similar), but I haven't done DLL's for Excel often enough to have memorised the details there.
The things I mentioned will still be problems, so I would assume that the "1 error" is a totally catastrophic one that means .Net can't even manage to parse the other code.
You do need the class (or something similar), but I haven't done DLL's for Excel often enough to have memorised the details there.
My main concern if I compile the macro to a dll will excel not throw errors on nrssheet ? The workbook have a sheet ("Nrs")
I have made those changes and only have 1 error now -thanks for your help there -thanks so much
This is the last error
Public Sub Getcombos() As Integer <<<<<End of Statement expected
My main concern if I compile the macro to a dll will excel not throw errors on nrssheet ? The workbook have a sheet ("Nrs")
Excel doesn't see the code in the DLL, as far as Excel is concerned actions are being performed on the equivalent of: Application.Sheets("Nrs")
...which is perfectly acceptable.
I have made those changes and only have 1 error now -thanks for your help there -thanks so much
I expected more than one, so that's a nice surprise.
This is the last error
Public Sub Getcombos() As Integer <<<<<End of Statement expected
There are two ways that could be changed, but to match the original macro simply remove the "As Integer":
Excel doesn't see the code in the DLL, as far as Excel is concerned actions are being performed on the equivalent of: Application.Sheets("Nrs")
...which is perfectly acceptable.
I expected more than one, so that's a nice surprise.
There are two ways that could be changed, but to match the original macro simply remove the "As Integer":
It worked perfectly but you were right I have a problem on the 1 error
Error 1 Array lower bounds can be only '0'. Seems this is not supported in VB 2002- 2005 ....So I have to compile this with VB 2010 to get past this error.......so close si_the_geek thank you so much for your help there YOU ROCK thank you
Right. In VBA and VB6, you could specify the array bounds, which could be anything. That is not the case in .NET. In .NET, ALL arrays start at 0, with no exceptions. In fact, ALL arrays always start at 0. The way that VBA and VB6 were allowing different array bounds was by doing a bit of simple math behind the scenes. For reasons that are largely philosophical, MS decided to drop that math for .NET, so it is what it is. This issue will be true for all versions of .NET (2002 on). While I felt that 2010 was the nicest looking, and most balanced version of Visual Studio, it won't change anything for you.
To solve this, you have to change those arrays that you set to 1 to whatever. They have to be essentially Redim Preserve (whatever -1). You then loop from 0 to array.Count -1 rather than 1 to array.Count, as you are currently doing.
Right. In VBA and VB6, you could specify the array bounds, which could be anything. That is not the case in .NET. In .NET, ALL arrays start at 0, with no exceptions. In fact, ALL arrays always start at 0. The way that VBA and VB6 were allowing different array bounds was by doing a bit of simple math behind the scenes. For reasons that are largely philosophical, MS decided to drop that math for .NET, so it is what it is. This issue will be true for all versions of .NET (2002 on). While I felt that 2010 was the nicest looking, and most balanced version of Visual Studio, it won't change anything for you.
To solve this, you have to change those arrays that you set to 1 to whatever. They have to be essentially Redim Preserve (whatever -1). You then loop from 0 to array.Count -1 rather than 1 to array.Count, as you are currently doing.
Thank you very much for the explanation I thought it was only not supported on the lower versions VB -seems all versions.....so basically I have a problem have to amend the code like you have explained here......I found this too but I have no idea how to implement it
I only had a quick glance over the code, but I think this is all that needs to change:
...which would become:
Code:
ReDim Preserve arrResults(0 To colResults.Count - 1)
For i = 1 To colResults.Count
arrResults(i - 1) = colResults(i)
Next i
(the "0 To" can be removed)
Fabulous thank you for your help there and if I may ask you my last noobish question how will I go by executing the macro. I am learning I will mark this as solved you aced it spot on thanks so much for your help there You are the BOMB you know your stuff thanks allot
Unfortunately that is one of the things I haven't done often enough to remember it... hopefully somebody else will help with that, alternatively I'm sure there will be tutorials on the web to help with that bit.
you aced it spot on thanks so much for your help there You are the BOMB you know your stuff thanks allot
Imports Excel = Microsoft.Office.Interop.Excel
Public Class DataWorkExcel
Protected _xlAppl As New Excel.Application
Public Sub runMainSub()
_xlAppl.Run("Modulname.Subname")
End Sub
End Class
regards
Chris
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.