"excel vba error 424 object required when calling sub" Code Answer's

excel vba error 424 object required when calling sub

By Excel Hero on Oct 29, 2020

'Excel VBA Error 424: Object required when calling sub.

'Error 424 is raised when an object is required but not provided.

'For the following Sub an argument of type Range is required. A Range is 
'an object in Excel VBA. So when calling this Sub, the calling code must
'pass a Range reference to the Sub:
  
Sub MySub(r As Range)

End Sub

'It is important to realize that in VBA an array is NOT an object and an
'array is certainly NOT a Range. If the calling code attempts to pass an 
'array to the above procedure, VBA will raise the 424 error. For example:

Dim v
v = Range("A1:A10").Value
MySub v		'<--This is an error (424) because 'v' points to an array.


'Also, be careful with parentheses:
  
Dim r as Range
r = Range("A1:A10")
MySub r		'<--This works
MySub (r)	'<--This doesn't work and raises Error 424.

'Placing parenthese around a variable argument when calling a Sub forces VBA
'to evaluate that variable BEFORE the call to the Sub takes place. When an
'object variable is evaluated, VBA creates a temporary variant array of
'all of the values in the object... and that variant array is then passed 
'to the Sub instead of the object... and since the Sub itself
'is expecting an object (in this case a Range object) VBA throws the error 424
'when the array is passed instead of the Range object. This mistake is common.
'
'
'Note: There is one caveat here, the 'Call' keyword:

Call MySub(r)	'<--This works
Call MySub((r))	'<--This doesn't work and raises Error 424.

'When using the 'Call' keyword to execute a Subroutine, parentheses must
'be used for the Sub's argument list (if it has arguments). But notice
'that there is NO SPACE between the name of the Sub and its argument
'list.
'
'
'


  

Source: academy.excelhero.com

Add Comment