CODEWITHSUNDEEP
excelvba
Noam Marciano
Noam Marciano

Reputation: 5

how to clear and activate cell as variables?

I tried to clear and to activate cell by function, but it doesn't works.

 Function cts(a) As Range
 If IsEmpty(a.Value) = True Then
     MsgBox ("ok")
 ElseIf IsNumeric(a.Value) = False Then
     MsgBox "non-numeric entry"
     Range("a").ClearContents
     Range("a").Activate
 ElseIf Int(a.Value) = False Then
     MsgBox "integer required"
     Range("a").ClearContents
     Range("a").Activate
 ElseIf a.Value > 5 Or a.Value < 1 Then
     MsgBox "valid values are between 1 and 5"
     Range("a").ClearContents
     Range("a").Activate
 Else:
     MsgBox "ok"
 End If

 End Function

the msg is shows up, but not the clear nor the activate.

Upvotes: 0

Views: 94

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

That a parameter is a Range object - it should be declared as such:

Public Function cts(ByVal a As Range) As Range

IsEmpty returns a Boolean; comparing it to a Boolean literal is redundant:

If IsEmpty(a.Value) = True Then

Should be:

If IsEmpty(a.Value) Then

The expression ("Ok") is uselessly being evaluated as a String value (it's already a string literal). Don't use parentheses when you discard a function's return value:

MsgBox "Ok"

a being a Range object, this:

Range("a").ClearContents
Range("a").Activate

Probably means to be this ("a" isn't a valid cell reference, so Range("a") can't be evaluated):

a.ClearContents
a.Activate

By declaring a As Range, you get IntelliSense and Ctrl+Space autocompletion for its member calls; in your code it's an implicit Variant, so the member calls are all resolved at run-time (and Option Explicit can't save you from a typo).

The function isn't returning anything, and it's not clear what Range you mean it to return (or why).

If you wanted it to return a, you would do it like this:

Set cts = a

If the procedure doesn't need to return anything, then it should be a Sub procedure, not a Function.

ElseIf Int(a.Value) = False Then

That Int function call will fail with a type mismatch error if a.Value contains a Variant/Error value, because the conditional block hasn't first evaluated whether IsError(a.Value) is true; also = False is coercing the Integer into a Boolean, so the expression will wrongly evaluate to False when a.Value is 0 (because zero is False, and any non-zero value is True). The Int function takes 123.456 and outputs 123, i.e. it returns the integer portion of the value it's given - it does not evaluate whether the argument is an integer or not.

In order to evaluate whether the value is an integer value, you need to first make sure you're looking at a numeric value:

If IsNumeric(a.Value) Then

Then compare Int(a.Value) with a.Value and see if they are the same:

    If Int(a.Value) = a.Value Then

Make sure you put the ElseIf a.Value > 5 Or a.Value < 1 Then check inside the IsNumeric case, otherwise that condition too will blow up given an error value, because Variant/Error can't be compared to anythign other than an Error value.

Upvotes: 1

Related Questions