PDA

View Full Version : VBA run-time error 438



hamza
01-10-2013, 10:55 AM
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.
http://www9.0zz0.com/2013/01/10/17/171375923.jpg
I've seen many solutions in the forum for this error but didn't manage to resolve my case ..

Any help please !

Kenneth Hobs
01-10-2013, 12:24 PM
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.
Call Whatever() though the usual case is to pass a parameter to a routine. e.g.
Call Whatever("Hi") Normally, I execute a routine like this:
Whatever and if it has a parameter to pass:
Whatever "Hi"
Notice that Whatever is not a function since nothing was returned. A function named whatever might be:
MsgBox Whatever
Rather than Select, just use the Range object. e.g.
x = Range("H27").value

hamza
01-11-2013, 03:26 AM
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
entre = Range("H25").Value
and then I used only the name of my routine to call it
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
'...

It's working perfectly .. Thank you

Aussiebear
01-11-2013, 04:19 PM
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