2

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

Community
  • 1
  • 1
Dulini Atapattu
  • 2,735
  • 8
  • 33
  • 47
  • 2
    The exact code works on my end. There is also the ClearContents function you can try. However, it looks like another issue at your end. What do you mean by locked property?Is the sheet/cell protected?In that case you have to use the unprotect command by providing the password. – Aziz May 29 '13 at 04:47
  • The sheet itself it not protected and also the same function used to work earlier which now does not work – Dulini Atapattu May 29 '13 at 05:38
  • Any ideas of possible reasons??? – Dulini Atapattu May 29 '13 at 05:44
  • where do you call it from- excel cell (will not work) or vba (immediate window of any other subroutine)? – Kazimierz Jawor May 29 '13 at 05:53
  • 1
    [limitations of custom functions in Excel](http://support.microsoft.com/kb/170787) – Santosh May 29 '13 at 06:05
  • [Workaround for calling custom functions](http://stackoverflow.com/questions/16262930/cleaning-cells-with-a-vba-function-in-excel/16263065#16263065) – Santosh May 29 '13 at 06:07
  • @KazJaw: I m calling the function - test() from a cell other than A1... can that be the problem? – Dulini Atapattu May 29 '13 at 06:22
  • obviously, this is the problem... in few simply words- you can't change other cells when calling function from excel cell. See some links from @Santosh above for better understanding... – Kazimierz Jawor May 29 '13 at 06:29
  • @KazJaw and Santosh: Many thanks for info, and if I want to clear the cell when the user requires, is there anyway of achieving this by using a Subroutine? Can I please have some suggestions? – Dulini Atapattu May 29 '13 at 06:36
  • @DuliniAtapattu, there are some correct Sub's in two answers below, you can use it (without `Volatile` line). The only problem is which way will the user trigger cleaning... See also @Santosh workaround link... – Kazimierz Jawor May 29 '13 at 06:58
  • @KazJaw and Santosh: Many thanks for your info. I ve got it working using subroutines and calling it in the workbook_BeforeClose subroutine. Thanks – Dulini Atapattu May 29 '13 at 07:05

2 Answers2

4
  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
Jüri Ruut
  • 2,500
  • 17
  • 20
  • Interesting. Any clues why it works on my end while being used as a function? – Aziz May 29 '13 at 12:12
  • If used as a cell formula, UDF cannot delete cell contents. If used from VBE, it's possible, however, a user may be unpleasantly surprised while discovering data in some cells irreversibly overwritten (Undo doesn't work after running VBA). – Jüri Ruut May 29 '13 at 13:53
1

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
KV Ramana
  • 89
  • 3
  • 11