Results 1 to 5 of 5

Thread: Need help with declaring routine and calling a dll from excel vba

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Need help with declaring routine and calling a dll from excel vba

    Goodday

    My question is related to this



    Hope someone can help. I would like to call a dll function from excel to execute the dll so it executes the code. I asked how do I call the dll from VBA but the code is wrong as there is no mention of lib or alias to call the dll. It was also declared wrong ????

    I need some help to do this please -very new to VB and a bit confused how I compile the macro to a dll and call it from excel.The macro do a calculation so its a function. It should be a public/private function declared ? In the link above its not a function and it doesnt call the dll so its incorect. Thank you for any help on this as its a simple project I would like to solve. The dll must be compiled as a com interop dll

    If I run =getcombos from excel it must execute the code in dll

    Lets say for example I want the macro to be coded to test.dll

    So i need the macro converted to a function with lib and alias and then call it from excel. Hope its more clearer

    Thank you for any pointers and help
    Last edited by JackBrown22; Nov 5th, 2017 at 11:02 AM.

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

    Re: Need help with declaring routine and calling a dll from excel vba

    Hi jack,

    I'm from Germany so I can't talk/show the Steps creating the DLL with my German version of VB.

    here a Link where the steps are shown...

    http://www.geeksengine.com/article/create-dll.html

    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    9

    Re: Need help with declaring routine and calling a dll from excel vba

    Quote Originally Posted by ChrisE View Post
    Hi jack,

    I'm from Germany so I can't talk/show the Steps creating the DLL with my German version of VB.

    here a Link where the steps are shown...

    http://www.geeksengine.com/article/create-dll.html

    regards
    Chris
    Hi Chris
    Thanks for your reply

    Thats in C.....the procedure is easy. I only need help with the declaration of my existing code from VBA to VB. It must be a function and NOT A SUB

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

    Re: Need help with declaring routine and calling a dll from excel vba

    I'm not sure whether this is in the best location, yet. You talk of macros and VBA, which are two terms that go well together, but you talk about this dll, written in VB. Which version of VB is the dll written in? If the dll is .NET, then is the question about writing the function, or is the question about calling it from VBA?

    If this makes more sense in the Office Development forum (VBA questions), then I'll move it, but if it's a .NET question, then it's in the right location.
    My usual boring signature: Nothing

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

    Re: Need help with declaring routine and calling a dll from excel vba

    Hi Jack,

    I am still not sure what you want ? perform a Caluclation, or Create a Dropdown of
    values from a Range ?
    you don't really need a DLL to do that.

    I have included both in this sample ....

    Code:
    Option Strict On
    
    Imports Excel
    
    Public Class Form1
        Public Enum Excel_Colors
            rgbAliceBlue = 16775408          'Alice Blue
            rgbAntiqueWhite = 14150650       'Antique White
            rgbAqua = 16776960               'Aqua
            rgbAquamarine = 13959039         'Aquamarine
            rgbAzure = 16777200              'Azure
            rgbBeige = 14480885              'Beige
            rgbBisque = 12903679             'Bisque
            rgbBlack = 0                     'Black
            rgbBlanchedAlmond = 13495295     'Blanched Almond
            rgbBlue = 16711680               'Blue
            rgbBlueViolet = 14822282         'Blue Violet
            rgbBrown = 2763429               'Brown
            rgbBurlyWood = 8894686           'Burly Wood
            rgbCadetBlue = 10526303          'Cadet Blue
            rgbChartreuse = 65407            'Chartreuse
            rgbCoral = 5275647               'Coral
            rgbCornflowerBlue = 15570276     'Cornflower Blue
            rgbCornsilk = 14481663           'Cornsilk
            rgbCrimson = 3937500             'Crimson
            rgbDarkBlue = 9109504            'Dark Blue
            rgbDarkCyan = 9145088            'Dark Cyan
            rgbDarkGoldenrod = 755384        'Dark Goldenrod
            rgbDarkGray = 11119017           'Dark Gray
            rgbDarkGreen = 25600             'Dark Green
            rgbDarkGrey = 11119017           'Dark Grey
            rgbDarkKhaki = 7059389           'Dark Khaki
            rgbDarkMagenta = 9109643         'Dark Magenta
            rgbDarkOliveGreen = 3107669      'Dark Olive Green
            rgbDarkOrange = 36095            'Dark Orange
            rgbDarkOrchid = 13382297         'Dark Orchid
            rgbDarkRed = 139                 'Dark Red
            rgbDarkSalmon = 8034025          'Dark Salmon
            rgbDarkSeaGreen = 9419919        'Dark Sea Green
            rgbDarkSlateBlue = 9125192       'Dark Slate Blue
            rgbDarkSlateGray = 5197615       'Dark Slate Gray
            rgbDarkSlateGrey = 5197615       'Dark Slate Grey
            rgbDarkTurquoise = 13749760      'Dark Turquoise
            rgbDarkViolet = 13828244         'Dark Violet
            rgbDeepPink = 9639167            'Deep Pink
            rgbDeepSkyBlue = 16760576        'Deep Sky Blue
            rgbDimGray = 6908265             'Dim Gray
            rgbDimGrey = 6908265             'Dim Grey
            rgbDodgerBlue = 16748574         'Dodger Blue
            rgbFireBrick = 2237106           'Fire Brick
            rgbFloralWhite = 15792895        'Floral White
            rgbForestGreen = 2263842         'Forest Green
            rgbFuchsia = 16711935            'Fuchsia
            rgbGainsboro = 14474460          'Gainsboro
            rgbGhostWhite = 16775416         'Ghost White
            rgbGold = 55295                  'Gold
            rgbGoldenrod = 2139610           'Goldenrod
            rgbGray = 8421504                'Gray
            rgbGreen = 32768                 'Green
            rgbGreenYellow = 3145645         'Green Yellow
            rgbGrey = 8421504                'Grey
            rgbHoneydew = 15794160           'Honeydew
            rgbHotPink = 11823615            'Hot Pink
            rgbIndianRed = 6053069           'Indian Red
            rgbIndigo = 8519755              'Indigo
            rgbIvory = 15794175              'Ivory
            rgbKhaki = 9234160               'Khaki
            rgbLavender = 16443110           'Lavender
            rgbLavenderBlush = 16118015      'Lavender Blush
            rgbLawnGreen = 64636             'Lawn Green
            rgbLemonChiffon = 13499135       'Lemon Chiffon
            rgbLightBlue = 15128749          'Light Blue
            rgbLightCoral = 8421616          'Light Coral
            rgbLightCyan = 9145088           'Light Cyan
            rgbLightGoldenrodYellow = 13826810   'LightGoldenrodYellow
            rgbLightGray = 13882323          'Light Gray
            rgbLightGreen = 9498256          'Light Green
            rgbLightGrey = 13882323          'Light Grey
            rgbLightPink = 12695295          'Light Pink
            rgbLightSalmon = 8036607         'Light Salmon
            rgbLightSeaGreen = 11186720      'Light Sea Green
            rgbLightSkyBlue = 16436871       'Light Sky Blue
            rgbLightSlateGray = 10061943     'Light Slate Gray
            rgbLightSteelBlue = 14599344     'Light Steel Blue
            rgbLightYellow = 14745599        'Light Yellow
            rgbLime = 65280                  'Lime
            rgbLimeGreen = 3329330           'Lime Green
            rgbLinen = 15134970              'Linen
            rgbMaroon = 128                  'Maroon
            rgbMediumAquamarine = 11206502   'Medium Aquamarine
            rgbMediumBlue = 13434880         'Medium Blue
            rgbMediumOrchid = 13850042       'Medium Orchid
            rgbMediumPurple = 14381203       'Medium Purple
            rgbMediumSeaGreen = 7451452      'Medium Sea Green
            rgbMediumSlateBlue = 15624315    'Medium Slate Blue
            rgbMediumSpringGreen = 10156544  'Medium Spring Green
            rgbMediumTurquoise = 13422920    'Medium Turquoise
            rgbMediumVioletRed = 8721863     'Medium Violet Red
            rgbMidnightBlue = 7346457        'Midnight Blue
            rgbMintCream = 16449525          'Mint Cream
            rgbMistyRose = 14804223          'Misty Rose
            rgbMoccasin = 11920639           'Moccasin
            rgbNavajoWhite = 11394815        'Navajo White
            rgbNavy = 8388608                'Navy
            rgbNavyBlue = 8388608            'Navy Blue
            rgbOldLace = 15136253            'Old Lace
            rgbOlive = 32896                 'Olive
            rgbOliveDrab = 2330219           'Olive Drab
            rgbOrange = 42495                'Orange
            rgbOrangeRed = 17919             'Orange Red
            rgbOrchid = 14053594             'Orchid
            rgbPaleGoldenrod = 7071982       'Pale Goldenrod
            rgbPaleGreen = 10025880          'Pale Green
            rgbPaleTurquoise = 15658671      'Pale Turquoise
            rgbPaleVioletRed = 9662683       'Pale Violet Red
            rgbPapayaWhip = 14020607         'Papaya Whip
            rgbPeachPuff = 12180223          'Peach Puff
            rgbPeru = 4163021                'Peru
            rgbPink = 13353215               'Pink
            rgbPlum = 14524637               'Plum
            rgbPowderBlue = 15130800         'Powder Blue
            rgbPurple = 8388736              'Purple
            rgbRed = 255                     'Red
            rgbRosyBrown = 9408444           'Rosy Brown
            rgbRoyalBlue = 14772545          'Royal Blue
            rgbSalmon = 7504122              'Salmon
            rgbSandyBrown = 6333684          'Sandy Brown
            rgbSeaGreen = 5737262            'Sea Green
            rgbSeashell = 15660543           'Seashell
            rgbSienna = 2970272              'Sienna
            rgbSilver = 12632256             'Silver
            rgbSkyBlue = 15453831            'Sky Blue
            rgbSlateBlue = 13458026          'Slate Blue
            rgbSlateGray = 9470064           'Slate Gray
            rgbSnow = 16448255               'Snow
            rgbSpringGreen = 8388352         'Spring Green
            rgbSteelBlue = 11829830          'Steel Blue
            rgbTan = 9221330                 'Tan
            rgbTeal = 8421376                'Teal
            rgbThistle = 14204888            'Thistle
            rgbTomato = 4678655              'Tomato
            rgbTurquoise = 13688896          'Turquoise
            rgbViolet = 15631086             'Violet
            rgbWheat = 11788021              'Wheat
            rgbWhite = 16777215              'White
            rgbWhiteSmoke = 16119285         'White Smoke
            rgbYellow = 65535                'Yellow
            rgbYellowGreen = 3329434         'Yellow Green
        End Enum
    
       
       
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim a As Integer
            Dim Exceldaten(9, 2) As String
            For a = 0 To 9
                Exceldaten(a, 0) = Chr(65 + a)
                Exceldaten(a, 1) = Chr(48 + a)
                Exceldaten(a, 2) = "Column 3 - Row " & (a + 1)
            Next
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oXL = CType(CreateObject("Excel.Application"), Excel.Application)
            oWB = oXL.Workbooks.Add
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
            'oXL.Visible = True
            oSheet.Range("A1").Value = "vbForums.com"
            oSheet.Range("B1").Value = "my Number"
            oSheet.Range("C1").Value = "Sssssss"
            'Excelformat
            oSheet.Range("A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("E1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("A1").ColumnWidth = 10
            oSheet.Range("B1").ColumnWidth = 10
            oSheet.Range("C1").ColumnWidth = 15.5
            With oSheet.Range("A1", "C1")
                .Font.Bold = True
                .Font.Color = Excel_Colors.rgbOrangeRed
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            End With
    
            'add something to calculate
            oSheet.Range("D2").Value = 10.25
            oSheet.Range("D3").Value = 101.25
            oSheet.Range("D4").Value = 123330.65
            oSheet.Range("D5").Value = 1255.25
          
            oSheet.Range("E6").Value = "20.10.2017 " & TimeOfDay
            oSheet.Range("E7").Value = String.Format("{0:dd.MM.yyyy HH:mm:ss}", Date.Now)
    
    
    
            'Dropdown Start ................
            'create a Dropdown , do you want to hide that column ?
            'Dim columnA As Excel.Range = CType(oSheet.Columns(2), Excel.Range)
            'columnA.Hidden = True
    
            'this will set the Dropdown Button in cell D4, but you could put it anywhere
            ' just change the (1,4) to something else
            Dim range As Excel.Range = CType(oSheet.Cells(1, 4), Excel.Range)
    
    
            'what Range ? in this case B2:B70
            range.Validation.Add(Type:=Excel.XlDVType.xlValidateList, _
                AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
                Operator:=Excel.XlFormatConditionOperator.xlBetween, _
                Formula1:="=$B$2:$B$70")
            'Dropdown End...............
    
    
            'Data to Excel 
            oSheet.Range("A2", "C9").Value = Exceldaten
            'Calulate values, 
            'in German = "=Summe(D2:D5)"
            'in Englisch probably = "=Sum(D2:D5)"
            oSheet.Range("D7").Value = "=Summe(D2:D5)"
            oSheet.Range("D7").NumberFormatLocal = "#.##0,00"
            oSheet.Range("D7").Interior.Color = Excel_Colors.rgbCadetBlue
    
            'Save Excel
            oSheet.SaveAs("C:\Exceldaten99.xls")
            oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub
    HTH
    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