Including a API in VBA(excel)

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?

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.

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.

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.

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?

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.

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

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?

Can you post the code?

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?

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.

ah excellente, thank you very very much for everything!

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

From the Excel Help file.

Code:

---

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

---