Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Calling dll from vba

Posted on 2000-04-03
9
844 Views
Last Modified: 2012-06-21
I have a spreadsheet which calls function from dll.Dll is registered by
addin.What I need is to call this function from VBA code.How do I write
declare statement for named ranges that this dll function accepts?I want to use "Declare" not "Run" worksheet function.
0
Comment
Question by:mdanny
9 Comments
 
LVL 13

Expert Comment

by:cri
ID: 2682644
Can not find the article for Office 97 but should you have this, try the procedure as given for Office 2000:

http://msdn.microsoft.com/library/officedev/odeopg/deovraccessingfunctionsindll.htm
0
 
LVL 8

Expert Comment

by:stochastic
ID: 2682679
mdanny,
Here is an example I did as my first attempt with DLLs to be called from VBA.

My DLL was called MyVBAdll.dll. It contained a function called CircumF, taking one Double (radius) as param and returning a double value as answer.

The sub CircumferenceTable is given just to show how the call to CircumF was made.

Hope this helps :-)
- stochastic

Declare Function CircumF Lib "D:\dlltrial\MyVBAdll.dll" _
    (ByVal radius As Double) As Double
   
Sub CircumferenceTable()
    Dim i As Double
   
    Worksheets(1).Activate
    Range("a1:b11").Clear
    Cells(1, 1) = "radius"
    Cells(1, 2) = "circumference"
    For i = 1 To 10
        Cells(i + 1, 1) = i
        Cells(i + 1, 2) = CircumF(i)
    Next
    Columns("a:b").AutoFit
End Sub

0
 
LVL 1

Author Comment

by:mdanny
ID: 2683036
I need to pass arrays(ranges) to func and back.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 8

Expert Comment

by:stochastic
ID: 2683074
If I understood you correctly, your function inside DLL expects an array as a parameter. But you want to pass as a parameter, a range in place of an array.

Is this correct?

I do not know how to do that, or whether this is possible. It is unlikely that you can pass named ranges directly as parameters to a DLL function. But I think the following should work:

1. Write a function (in VBA) that will accept a range as a parameter.

2. Inside this function, declare an array and load it with the contents of the range, using VBA statements.

3. Once you have got the array, call the function inside the DLL with this array. [I assume you know how to do this].

4. When the DLL function returns, the array will have been modified if appropriate (unless you passed it BYREF).

5. Dump the contents of that array into your source range, if that is what you need.

Am I on the right track? I apologize if I completely misunderstood.

If you need examples of how to write a VC++ project that will generate a DLL functions callable from VBA, there are samples available, including those involving array parameters.

- stochastic
0
 
LVL 1

Author Comment

by:mdanny
ID: 2683194
I need to pass arrays(ranges) to func and back.
0
 
LVL 1

Author Comment

by:mdanny
ID: 2683222
The problem is I haven't wrote the dll so I can't change it's input parameters.
When I use it from spreadsheet I pass namws of ranges.The register looks like
=REGISTER("dllname.dll","thisfunc","JKRP",etc)
0
 
LVL 8

Expert Comment

by:stochastic
ID: 2683224
mdanny,

>I need to pass arrays(ranges) to func and back.

Yes, I saw this comment the last time, and I thought I was responding to that! Is something amiss here? Can someone else help clarify?

- stochastic
0
 
LVL 1

Author Comment

by:mdanny
ID: 2697708
Adjusted points from 50 to 58
0
 
LVL 1

Accepted Solution

by:
kuk010998 earned 58 total points
ID: 2705438
The third par to REGISTER is important here. Got to the REGISTER dox to check it out. Excel addin DLLs can use Excel-specific types, you cannot (directly) Declare equivalents in VBA. You could always hack, with CopyMemory and so on, but first you gotta understand what the DLL wants, and "JKRP" is a description to that.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question