Results 1 to 15 of 15

Thread: [RESOLVED] Need help please Vba macro to dll for Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Resolved [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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Need help please Vba macro to dll for Excel

    Welcome to VBForums

    Thread moved from the 'VB6 and Earlier' forum to the 'VB.Net' (VB2002 and later) forum


    What are the error(s) you are getting, and which line(s) of code do they refer to?

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by si_the_geek View Post
    Welcome to VBForums

    Thread moved from the 'VB6 and Earlier' forum to the 'VB.Net' (VB2002 and later) forum


    What are the error(s) you are getting, and which line(s) of code do they refer to?
    Thank you so much. I dont understand it it gave me 33 errors....example attached. Seems like its complaining about things not being declared ????
    Attached Images Attached Images  

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Need help please Vba macro to dll for Excel

    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:
    Code:
        rngNumbers = NrsSheet.Range("B2", NrsSheet.Cells(NrsSheet.Rows.Count, "B").End(xlUp))

    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)

  5. #5

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by si_the_geek View Post
    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:
    Code:
        rngNumbers = NrsSheet.Range("B2", NrsSheet.Cells(NrsSheet.Rows.Count, "B").End(xlUp))

    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 ?

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Need help please Vba macro to dll for Excel

    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by si_the_geek View Post
    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

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by JackBrown22 View Post
    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":

  9. #9

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by si_the_geek View Post
    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

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

    Re: Need help please Vba macro to dll for Excel

    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.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by Shaggy Hiker View Post
    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

    https://blogs.msdn.microsoft.com/vbf...-lower-bounds/

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Need help please Vba macro to dll for Excel

    I only had a quick glance over the code, but I think this is all that needs to change:
    Code:
            ReDim Preserve arrResults(1 To colResults.Count)
                    For i = 1 To colResults.Count
                        arrResults(i) = colResults(i)
                    Next i
    ...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)

  13. #13

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by si_the_geek View Post
    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

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Need help please Vba macro to dll for Excel

    Quote Originally Posted by JackBrown22 View Post
    how will I go by executing the macro.
    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
    I'm happy to help

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

    Re: Need help please Vba macro to dll for Excel

    Hi,
    should go like this...

    how will I go by executing the macro.
    Modul and Sub have to be in your Woorkbook
    Code:
    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.

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