VBA run-time error 438

[hamza]

Hi everyone,

"Runtime error 438 - Object doesn't support this property or method"

I'm writing a VB code in Macro for my Excel (2013) to avoid doing some operations manually.
I made a function "Deblai" and want to call it into an other Sub.
I think my problem is to find the right syntax to call the function "Deblai" correctly.

I've seen many solutions in the forum for this error but didn't manage to resolve my case ..

Any help please !

[Kenneth Hobs]

Please post code between VBA code tags.

You have several issues going on. Functions must return a value. Neither your function returns a value nor does your Sub get a value from the function.

If you are doing things like Sort, use a Sub.

I like to use a rule for the use of Call. Use ()'s if you use Call by itself on a line. e.g.
[vba]Call Whatever()[/vba] though the usual case is to pass a parameter to a routine. e.g.
[vba]Call Whatever("Hi")[/vba] Normally, I execute a routine like this:
[vba]Whatever[/vba] and if it has a parameter to pass:
[vba]Whatever "Hi"[/vba]
Notice that Whatever is not a function since nothing was returned. A function named whatever might be:
[vba]MsgBox Whatever[/vba]
Rather than Select, just use the Range object. e.g.
[vba]x = Range("H27").value[/vba]

[hamza]

Thank you Mr Kenneth, you saved my life
As you have said, many errors were there (not strang from a biginner like me)
First, I've replaced Select by only Range object
[VBA]entre = Range("H25").Value[/VBA]
and then I used only the name of my routine to call it
[VBA]Sub Deblai()
Dim entre, sort, temp, d As Double
sort = Range("H26").Value
entre = Range("H25").Value
d = Abs(sort - entre)
While d > 0.0001
Range("H27").Value = sort
entre = sort
sort = Range("H26").Value
d = Abs(sort - entre)
Wend
End Sub

Function row_min(b As Double) As Double
Dim oldFs, newFs, a, Fd, Fg As Double
Dim flag As Integer
Range("H18").Value = b
Deblai
oldFs = Range("H26").Value
a = Range("H17").Value
Range("H17").Value = a + 0.5
Deblai
Fd = Range("H26").Value
Range("H17").Value = a - 0.5
Deblai
'...[/VBA]

It's working perfectly .. Thank you

[Aussiebear]

Please take an opportunity to visit Chip Pearson's site as it also offers great advice.http://www.cpearson.com/excel/DeclaringVariables.aspx In this case the point is how to declare variables.

Pay Attention To Variables Declared With One Dim Statement
VBA allows declaring more than one variable with a single Dim statement. I don't like this for stylistic reasons, but others do prefer it. However, it is important to remember how variables will be typed. Consider the following code:
Dim J, K, L As Long
You may think that all three variables are declared as Long types. This is not the case. Only L is typed as a Long. The variables J and K are typed as Variant. This declaration is functionally equivalent to the following:
Dim J As Variant, K As Variant, L As Long
You should use the As Type modifier for each variable declared with the Dim statement:
Dim J As Long, K As Long, L As Long