VBA to VB 6.0

[quayz15]

I am new to programming. I have recently written a code in VBA for excel, and I need to write the same code in VB 6.0. I believe my trouble is I'm not sure how to define the element of an array as variables and use them in a function.
Any help is appreciated. the variables I am having trouble with is time x and xdot as shown.

Option Explicit

Private Sub Command1_Click()


Dim s As Integer
Dim xddot As Double
Dim timestep As Integer
Dim z, h As Double
Dim row1, endrow As Integer
Dim time, x, xdot As Double
Dim time1, x1, xdot1 As Double
Dim deltatime, deltaxdot As Double

s = Range(Range("C14"), Range("C65536").End(xlUp)).Count


z = 1
x = 0
xdot = 0
xddot = 0
x1 = 0
xdot1 = 0

endrow = Range("A65536").End(xlUp).ROW

For timestep = 1 To s

time = Range("A14").Offset(timestep - 1, 0).Value
x = Range("B14").Offset(timestep - 1, 0).Value
xdot = Range("C14").Offset(timestep - 1, 0).Value
time1 = Range("A14").Offset(timestep - 2, 0).Value
x1 = Range("B14").Offset(timestep - 2, 0).Value
xdot1 = Range("C14").Offset(timestep - 2, 0).Value
deltatime = time - time1
deltaxdot = xdot - xdot1


On Error Resume Next
xddot = deltaxdot / deltatime
If Err.Number <> 0 Then
xddot = 0
End If

Range("F14").Offset(timestep - 1, 0).Value = xddot

Dim k1, k2, k3, k4 As Double

h = deltaxdot


k1 = h * f(deltaxdot, z)
k2 = h * f(deltaxdot + h / 2, z + k1 / 2)
k3 = h * f(deltaxdot + h / 2, z + k2 / 2)
k4 = h * f(deltaxdot + h, z + k3)
z = z + k1 / 6 + k2 / 3 + k3 / 3 + k4 / 6

Range("G14").Offset(timestep - 1, 0).Value = z

Dim f0 As Double
Dim ft As Double
f0 = 0
ft = fc(z, x, xdot, xddot) - f0
Range("E14").Offset(timestep - 1, 0).Value = ft


Next timestep
End Sub
Function f(deltaxdot, z) As Double
Dim A As Double
Dim n As Double
Dim gamma As Double
Dim betta As Double

A = Range("D2").Value
n = Range("E2").Value
gamma = Range("F2").Value
betta = Range("G2").Value

If z > 0 And deltaxdot > 0 Then
f = A - (z ^ n) * (gamma + betta)
ElseIf z > 0 And deltaxdot < 0 Then
f = A + (z ^ n) * (gamma - betta)
ElseIf z < 0 And deltaxdot < 0 Then
f = A - Abs(z ^ n) * (gamma + betta)
ElseIf z < 0 And deltaxdot > 0 Then
f = A + Abs(z ^ n) * (gamma - betta)
End If


End Function


Function c(xdot) As Double
Dim a1 As Double
Dim a2 As Double
Dim p As Double

a1 = Range("H2").Value
a2 = Range("I2").Value
p = Range("J2").Value

c = a1 * Exp(-(a2 * Abs(xdot)) ^ p)

End Function

Function fc(z, x, xdot, xddot) As Double
Dim alfa As Double
Dim k As Double
Dim m As Double

alfa = Range("K2").Value
k = Range("L2").Value
m = Range("M2").Value

fc = alfa * z + k * x + c(xdot) * xdot + m * xddot

End Function

 

[HughLerwill]

The code as posted appears to have been pasted from VBA; it will require some modification before it will run under VB6.
You will have to create an Excel Application Object and then qualify all references to Excel Objects in your code with it. The basic template could be something like this;

Private Sub Command1_Click()

'create Excel Application object early or late bound

'1) Early bound is good for debugging with intellisense but requires project ref into a specific Excel version library and makes the vb6 app Excel version dependent
'Dim xlObj As New Excel.Application

'2) Late bound is not good for debugging but does not require project ref and vb6 app is not dependent on specific Excel version
Dim xlObj As Object
Set xlObj = CreateObject("Excel.Application")

'do stuff to it
With xlObj
.Visible = True 'if you want to see it
'create/ load a WorkBook
With .Workbooks.Open(App.Path & "\Book1.xls")
'access a sheet within it
With .Sheets(1)
'do stuff to the sheet
If .Cells(1, 1) = "Hello" Then
.Cells(1, 1) = "GoodBye"
Else
.Cells(1, 1) = "Hello"
End If
End With
.Save 'the WorkBook
End With
.Quit 'the Application; if you want to
End With

End Sub

When you have code looking broadly like that above you can concentrate on problems you think you may have now with x and xdot.

 

[quayz15]

Can I do this say if I save the data as a .txt file with the first column time second x and third xdot? Using somthing like
Input #1, time, x, xdot(I'm not sure if this will work or if I need 3 seporate inputfiles.)
In the end I would like to stay away from excel all together, but I dont know how step through the .txt file and use the data to calc the delta xdot and UDFs. Thanks

 

[eVAPor8]

Quayz15,

you should be able to use the wbk.SaveAs method to save in a .txt or .csv format.

Change

With .Workbooks.Open(App.Path & "\Book1.xls")

to

Dim wbk 'As Workbook ' if early-bound
Set wbk = .Workbooks.Open(App.Path & "\Book1.xls")
With wbk

Then later ...

wbk.SaveAs "c:\path\Filename.csv", xlCSV

Also, a little Gotcha in case you didn't know...

Dim time, x, xdot As Double

If you Dim x VB will dim as Variant.

Now if you
Dim time, x, xdot As Double

it looks like time, x and xdot are all Doubles.

WRONG!

time and x are actually Variants and xdot is the only Double. you might already know this, but it catches out a LOT of new proggers!

I used to be the Comaboy on Tek-Tips.

...And the despicable Jeremy Vyle elsewhere.

 

[HarleyQuinn]

If you're using late binding (as per New PosteVAPor8's example) then xlCSV won't be a valid enumeration, you'll have to either create your own for this purpose or use the actual value. Which in this case is 6.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

In response to your last question, if you save it as a tab delimited text file you can then read in the line and split() it into it's resultant values (you'd be splitting on a tab char).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com