-
Nov 5th, 2017, 03:16 AM
#1
Thread Starter
New Member
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.
-
Nov 5th, 2017, 06:08 AM
#2
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.
-
Nov 5th, 2017, 07:52 AM
#3
Thread Starter
New Member
Re: Need help with declaring routine and calling a dll from excel vba
Originally Posted by ChrisE
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
-
Nov 5th, 2017, 11:17 AM
#4
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
-
Nov 5th, 2017, 11:38 AM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|