CODEWITHSUNDEEP
excelvba
Dulini Atapattu
Dulini Atapattu

Reputation: 2735

Excel VBA - Range.Clear does not clear cell

I have the following function in my VBA which tries to clear the cell A1 in Excel Sheet1, but it does not clear the contents and also returns False as output:

Function test()
    Application.Volatile (False)    
    test = Worksheets("Sheet1").Range("A1:A1").Clear
End Function

Also please not the Locked property is True at this time and even if I make it False, the next line if I print it, the Locked property is True.

Any suggestions?

Thanks

Upvotes: 2

Views: 25194

Answers (2)

Jüri Ruut
Jüri Ruut

Reputation: 2530

  1. Function in VBA is generally a variable having a data type. In your example, data type is missing, meaning that the data type is set to Variable.
  2. Apparently, your data type happens to be boolean, so a function is set to False.

  3. It is a bad style to use functions for changing cell contents. However, your code works without problems as a Sub:

    Sub test()  
  Application.Volatile (False)  
  Worksheets("Sheet1").Range("A1:A1").Clear  
End Sub

Upvotes: 4

KV Ramana
KV Ramana

Reputation: 89

I am not sure exactly but as far as i know you cannot format excel sheet in Function instead we can do it using Subroutine.

Sub test()
  Worksheets("sheet1").Range("A1").Clear
End Sub

Upvotes: 1

Related Questions