VBA+Fortran

[alinerocha]

Hi everyone!

I'm trying to build an example using the VB and fortran.
The code is:

Declare Sub FinVel Lib "Dll" (m1 As Double, v1i As Double, m2 As Double, vf As Double)
Sub Velocity()
Dim m1 As Double, v1i As Double, m2 As Double, vf As Double

m1 = Range("b2").Value
v1i = rante("b3").Value
m2 = Range("b4").Value

Call FinVel(m1, v1i, m2, vf)

Range("b7").Value = vf

End Sub

and the fortran subroutine is:
subroutine FinVel(m1,v1i,m2,vf)
real(8) m1,v1i,m2,vf

!MS$ ATTRIBUTES DLLEXPORT:: FinVel
!MS$ ATTRIBUTES ALIAS: 'FinVel'::FinVel

vf=m1*v1i/(m1+m2)
end subroutine

My problem is that I don't know WHERE and HOW to save the subroutine.
What is the path that I have to put before LIB in my VB code?

Thank you,
Aline

[maciej]

The FORTRAN part should be compiled as dll - just start the project as DLL and build it.

The VB part should point to the built dll in the declaration:
Declare Sub FinVel Lib "here_full_pathname.dll"

Or the dll should be in the same directory where the VB code runs.

And I would use it so:
Private Declare Sub FinVel Lib "here_full_pathname.dll" (m1 As Double, v1i As Double, m2 As Double, vf As Double)

Maciej

[alinerocha]

Ok, but when I built my code itdidn't appear a file .dll.
What is the name that I have to use?

Thank you,

Aline

[maciej]

I started my dll naming it Fort-Dll1 so using VS2008 the path to the dll is:
...Visual Studio 2008ProjectsFort-Dll1Fort-Dll1Debug

if you set build option as "Release" it will be in ...Release folder.

You should easily find that file

regards,
Maciej

[alinerocha]

Now the following error message appears:

Can't find dll entry point in "D:ddd"

What should I do to fix it????

Thanks,

Aline

[anthonyrichards]

The attached EXCEL file makes use of a Velocity function in cell B7. This function calls a Fortran function FinVel in dynamic link library MyVBADLL.dll assumed to be in the same directory as the Excel file (in this case the root C: directory). The Fortran code I used to create the DLL follows. You can use it to create your own IVF project. For test purposes I includemy Release DLL in the attached ZIP file:

! MyVBADLL.f90
!
! FUNCTIONS/SUBROUTINES exported from MyVBADLL.dll:
! MyVBADLL -Function
!
Real(8) Function FinVel(m1,v1i,m2)
use ifwin
real(8) m1,v1i,m2
REAL(8) vf
integer iret

!MS$ ATTRIBUTES DLLEXPORT:: FinVel
!MS$ ATTRIBUTES STDCALL,ALIAS: 'FinVel'::FinVel
!MS$ ATTRIBUTES VALUE:: M1,V1I,M2

iret=MessageBox(0,'FinVel Entered OK'C,'Finvel Entered'c, MB_OK)

vf=m1*v1i/(m1+m2)
FinVel=vf
RETURN

end FUNCTION

Note that it is a FUNCTION rather than a SUBROUTINE and it returns the velocity value as a REAL(8) or VB Double.
Note the calling convention is STDCALL and the function arguments are called by value. The Visual Basic code used in a module in the EXCEL worksheet to call this function is as follows:

Declare Function FinVel Lib "C:MyVBADLL.dll" (ByVal m1 As Double, ByVal v1i As Double, ByVal m2 As Double) As Double

Function Velocity() As Double
Dim m1 As Double, v1i As Double, m2 As Double, vf As Double

m1 = Range("b2").Value
v1i = Range("b3").Value
m2 = Range("b4").Value

MsgBox ("Velocity Function, So far so good 1!")
MsgBox ("Velocity Function, m1 =" & Str(m1))
MsgBox ("Velocity Function, v1i =" & Str(v1i))
MsgBox ("Velocity Function, m2 =" & Str(m2))

vf = FinVel(m1, v1i, m2)
MsgBox ("Velocity Function, vf =" & Str(vf))
MsgBox ("Velocity Function, So far so good 2!")
Velocity = vf
End Function

The EXCEL file is populated with input values in cells B2,B3 and B4 and a call to the Velocity() function in cell B7 to return the velocity value. Apparently it is forbidden to return a function value AND at the same time return a value to a specified cell in the worksheet. I have tried it and it fails. So to do this, a call to a subroutine is needed. This is done in the EXCEL worksheet by adding a command button 'CommandButton1' that calls subroutine GetVelocity(). This subroutine returns a velocity value to cell B6 whenthe buttonis clicked. The code is as follows:

Private Sub CommandButton1_Click()
MsgBox ("Command Button1 clicked - calling GetVelocity Function")
Call GetVelocity
End Sub

Sub GetVelocity()
Dim m1 As Double, v1i As Double, m2 As Double, vf As Double

m1 = Range("b2").Value
v1i = Range("b3").Value
m2 = Range("b4").Value

MsgBox ("GetVelocity Subroutine,So far so good 1!")
MsgBox ("GetVelocity, Subroutine m1 =" & Str(m1))
MsgBox ("GetVelocity, Subroutinev1i =" & Str(v1i))
MsgBox ("GetVelocity, Subroutinem2 =" & Str(m2))

vf = FinVel(m1, v1i, m2)
MsgBox ("GetVelocity Subroutine,vf =" & Str(vf))
Range("B6") = vf

MsgBox ("GetVelocity Subroutine, So far so good 2!")

End Sub

[alinerocha]

Thank you fellow!

Now my programms work!

Regards,
Aline

[alinerocha]

Hi, I'm here again!

Now I can run my programs, but I would like to know if is it possible to comunicate (see the value of some variables), during the execution of the program. If the answer is Yes, how do I do this?

Thank you so much!

See you,

Aline

[DavidWhite]

Quoting - alinerocha

Hi, I'm here again!

Now I can run my programs, but I would like to know if is it possible to comunicate (see the value of some variables), during the execution of the program. If the answer is Yes, how do I do this?

Thank you so much!

See you,

Aline

Do you mean you want to run the DLL in debug mode?

If so, you need to set a break point on the first executable line in the DLL.

Then in VS set up the debug execution command to run excel.exe

When you start running in debug mode, Excel will start, then open your workbook which calls the DLL. If all goes well, when you get to the break point in the DLL, you will be back in VS and can watch the variables, etc.

You may need to do some more tweeking, such as setting the library path in your VBA code to point to the debug version of the DLL, etc.

Regards,


David

[alinerocha]

Quoting - David White

Do you mean you want to run the DLL in debug mode?

If so, you need to set a break point on the first executable line in the DLL.

Then in VS set up the debug execution command to run excel.exe

When you start running in debug mode, Excel will start, then open your workbook which calls the DLL. If all goes well, when you get to the break point in the DLL, you will be back in VS and can watch the variables, etc.

You may need to do some more tweeking, such as setting the library path in your VBA code to point to the debug version of the DLL, etc.

Regards,


David

I added a break point as you said, but nothing different happened when I run the excel.
How do I set the library path?

Thanks,

Aline

[DavidWhite]

Quoting - alinerocha

I added a break point as you said, but nothing different happened when I run the excel.
How do I set the library path?

Thanks,

Aline

In your VBA code, you should have something like

Private Declare Sub MySub Lib "MyDLL.dll" (X As Double)

Where there is no specified path, as here, the DLL must be locatable on your Windows PATH.

Alternatively, you can specify an absolute location by giving the full path and DLL name in the string instead. this would need to point to the DLL in your Debug Folder ... something like "C:My ProjectsMyDLLDebugMyDLL.dll"

Regards,

David

[alinerocha]

Quoting - David White

In your VBA code, you should have something like

Private Declare Sub MySub Lib "MyDLL.dll" (X As Double)

Where there is no specified path, as here, the DLL must be locatable on your Windows PATH.

Alternatively, you can specify an absolute location by giving the full path and DLL name in the string instead. this would need to point to the DLL in your Debug Folder ... something like "C:My ProjectsMyDLLDebugMyDLL.dll"

Regards,

David

Oh yes, I did this, but it didn't work!
Are theremore things to do?

Thanks,
Aline

[DavidWhite]

Can you check
1. That the latest version of the DLL on your machine is the Debug version
2. That the Excel VBA call is using the Debug version, not the Release version
3. Just to make sure, delete the release version of the DLL so that the only copy is the Debug version

Then try again. If Excel is trying to load the wrong DLL, then you should get an error message.

Have you tried stepping through the VBA code to check that it is getting to the call to the DLL function?

Regards,


David

[alinerocha]

Quoting - David White

Can you check
1. That the latest version of the DLL on your machine is the Debug version
2. That the Excel VBA call is using the Debug version, not the Release version
3. Just to make sure, delete the release version of the DLL so that the only copy is the Debug version

Then try again. If Excel is trying to load the wrong DLL, then you should get an error message.

Have you tried stepping through the VBA code to check that it is getting to the call to the DLL function?

Regards,


David

David,
I checked the points that you mentioned and it's everything right.
I know that the DLL has been called because the results change when I run the subroutine.
I really don't know why the breakpointsdon't work!

Thanks,

Aline

[DavidWhite]

Quoting - alinerocha

David,
I checked the points that you mentioned and it's everything right.
I know that the DLL has been called because the results change when I run the subroutine.
I really don't know why the breakpointsdon't work!

Thanks,

Aline

Aline,

Breakpoints will only work if you are running from within Visual Studio AND you are running the Debug version of the DLL in Debug mode.

That's why I wrote about deleting the other versions of the DLL to make sure that the Debug version is the one that is used.

You need to call Excel from within Visual Studio, then open your workbook to call the DLL. Opening Excel outside of Visual Studio will not cause the DLL to stop at the breakpoints.

You need to add the command for Excel in your Project Properties under Configuration Properties / Debugging / Action / Command -- make sure that you enter the command here for the Debug Configuration, not Release.

Regards,

David