Including a API in VBA(excel)

[Tuneman]

Somewhat new to VBA, I have experience in C++. Anyways I am trying to connect to a server through an API.

I have instruction on how to include some .dll files when using visual basic editor.

However, I am using excel and VBA, I assume all the code is going to be the same, however I dont know how to add these .dll files. The graphical interface isn't the same. Can't I do this all within the source code?

[Lord Orwell]

the graphical interface between vb6 and excel's vba is the same. Go into the vba macro editor and create a module. Put vb6-style declarations in the module.

[RobDog888]

Welcome to the Forums.

If you have some dlls that you need to reference in Excel's VBA then go to the Tools > References... menu and select the dlls you need to add.

[Tuneman]

hey, ya i had tried that, i cant seem to add any .dlls. It says I cannot add a reference to the specified file. Now I just noticed that they are in a vb.net folder. Perhaps this could be the problem?

bu then again some dlls that arent in the folder cannot be added either.

[RobDog888]

It depends on the dll. A .NET dll will definately not be reference-able if its not designed to be COM visible. Other dlls will depend on their code. There are C++ and ActiveX dlls. Is there a specific dll your trying to ref?

[Tuneman]

Yes they are API's to connect to a brokerage server. They do have an ActiveX file that I can properly refrenece. However, when I include that, and then try to run a function should be defined by that library, it cannot find that function. It's not a big deal i think I have found a way around it. Thank you though.

[RobDog888]

What was your wayu around it? Something specific to that app?

[Tuneman]

Yes they have a template excel file that connects to the app. I am going to write some vba code that interacts with this excel file and its spreadsheets instead.

I have one question though, I wrote a code that copies the value of a cell into another cell, then moves down 1 cell, and does the same thing. I want this macro to run every time I have the excel file open. however, when I run this macro, it locks me out from doing anything else with excel.

How do I get it so that I can be doing other stuff while its running that macro?

[RobDog888]

Can you post the code?

[Tuneman]

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim i As Integer

Sub Module1()
For i = 0 To 100
Cells(67 + i, 2) = Range("O13").value
Cells(67 + i, 3) = Range("P13").value
Sleep 5000
Next
End Sub



O13 and P13 and numbers that are constantly changing, and i want to record the value of those cells every few seconds. When i run this though, they stop updating.

I bet it has something to do with the sleep function eh?

[RobDog888]

Yes, the Sleep API stops the processing on your thread while its sleeping.
If you need a delay in the loop then there are other ways to do it like the Timer function in Excel.

[Tuneman]

ah excellente, thank you very very much for everything!

[Tuneman]

Is the timer function defined automatically, or do I have to create it?

[RobDog888]

From the Excel Help file.

Dim PauseTime, Start, Finish, TotalTime
If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
    PauseTime = 5    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop
    Finish = Timer    ' Set end time.
    TotalTime = Finish - Start    ' Calculate total time.
    MsgBox "Paused for " & TotalTime & " seconds"
Else
    End
End If