2

Problem

A dataview sheet I'm working on regularly wants to reset the contents of parts of the view (to be blank). Historically, we've had problems with users saying this sheet clears their system clipboard.

It looks like there's a problem with using Range.ClearContents to clear these values.

# Grab some data from cells onto the clipboard

Sub ClearTheClipboardWhenTheUserIsntExpectingIt()
    Cells(1, 1).EntireRow.ClearContents    ' Or something like that
End Sub

This should be enough to replicate the problem.

Workaround

My question is about a possible workaround, which is to do:

Dim r as Range
...
r.Value2 = Empty

Question(s)

  • Is there some obvious reason why this is not a reasonable workaround?
  • Is there some more canonical way to clear the contents of a set of cells without clearing the clipboard?
  • Am I just using ClearContents wrong?

I'm loathed to go through a large codebase and search/replace this behaviour if it's going to turn out later that I have to go back and fix it because I've introduced some bug.

Edit: I should mention that I'm using Excel 2007

jelford
  • 2,625
  • 1
  • 19
  • 33
  • I have tried to replicate this in xl2010 but failed. Using `Rows(1).ClearContents` is a little cleaner but I don't see why your current code would be failing. – brettdj Dec 21 '12 at 11:09
  • 1
    Out of curiosity does `Rows(1).Value = vbNullString` exhibit different behaviour? – brettdj Dec 21 '12 at 11:20
  • I think `Empty` seems more semantically correct. I want the cells to be empty. I *did* believe that Excel would distinguish between that and an empty string, but I can't seem to verify it, so it's probably not true – jelford Dec 21 '12 at 13:26

2 Answers2

2

Update: I have just tried the below code, found here, in a vanilla Excel spreadsheet with success:

Sub Button1_Click()

Dim clipboardText As String
clipboardText = GetTextFromClipboard()

Cells(1, 1).EntireRow.ClearContents

CopyTextToClipboard (clipboardText)

End Sub

Sub CopyTextToClipboard(ByVal inText As String)

Dim objClipboard As Object
Set objClipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objClipboard.SetText inText
objClipboard.PutInClipboard
Set objClipboard = Nothing

End Sub


Function GetTextFromClipboard() As String

Dim objClipboard As Object
Set objClipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objClipboard.GetFromClipboard
GetTextFromClipboard = objClipboard.GetText
Set objClipboard = Nothing

End Function
JMK
  • 27,273
  • 52
  • 163
  • 280
  • Sadly, this has a tricky bug with it too (I spent some time yesterday looking to make a clipboardhandler class to do this). If the code gets called from a workbook that doesn't reference `MSForms`, then it will throw when `MyData.PutInClipboard` gets called. Since I don't know where else my sheet is getting called, this is unnacceptable :( – jelford Dec 21 '12 at 11:08
  • 1
    Hmm check out the late binding section [here](http://desmondoshiwambo.wordpress.com/2012/02/23/how-to-copy-and-paste-text-tofrom-clipboard-using-vba-microsoft-access/), i think it will do this without needing a reference. – JMK Dec 21 '12 at 11:35
  • Interesting - that looks like a perfectly reasonable solution that would let me use a `DataObject`, but when I try it out I still get a `NotImplemented` error. Which is a shame, because that would have been a neat solution. – jelford Dec 21 '12 at 13:23
1

The only thing I notice is that it .ClearContents clears the MSO clipboard, which is different than Windows's clipboard. This is because .ClearContents for some reason sets CutCopyMode to False.

I would recommend either of these:

Sub test()
    Cells(1, 1).EntireRow.Value = vbNullString
End Sub

Sub test3()
    Cells(1, 1).EntireRow.Value = Empty
End Sub

Although I like brettdj's method better just because it's cleaner (Rows(1).Value = vbNullstring).

Also, from the help files:

Empty

Indicates that no beginning value has been assigned to a Variant variable. An Empty variable is represented as 0 in a numeric context or a zero-length string ("") in a string context.

Apparently when a cell value is set to Empty, it is considered a string value I supposed because when I tested it, it didn't set the cell to 0. So, when applying Empty or vbNullString to a cell value, there is pretty much no difference between them.

Joseph
  • 5,070
  • 1
  • 25
  • 26
  • `Cells(1, 1)` was just my way of saying "Some range passed in from elsewhere." The example was to show how to replicate the problem. Also, note that the documentation you quote implies `vbNullString` is **not** the same as `Empty`, for example it can successfully cast to an `int`, while `Int(vbNullString)` is a type error. – jelford Dec 21 '12 at 17:19
  • @jelford sorry, you're right that they are not the same. What I meant to say was that it has the same effect when setting the cell's value. I'll edit the content to reflect that. – Joseph Dec 21 '12 at 19:37