A Few VBA for Excel Questions

[TheRobster]

Hi All,

I have a few questions so I put them all in one post. Any help is, as always, appreciated.

1)
What's the best variable type to store a decimal number?

I tried dimensioning it as a decimal but found I can't do this. I'm using the type Double at the moment and it works but wondered if there was a better type to use to store a standard decimal figure (as well as integers of course)

2)
Is there a command to clear a textbox? At the moment I have an error handling routine which checks to see if the entry into a textbox is a number or not (floating point) and if it isn't, it brings up a message box that says 'That is not a number!' or something along those lines.

But I want to include a line before that which clears the textbox. I tried making the texbox.value = "" but the computer still thinks that's an error for some reason.

Is there something like a textbox.clear command?

3)
If I select a range using VBA code, perform some action on that range, how then do I deselect the range after the calculation has finished?

At the moment my code kinda goes like this:

Range("A1:A20).Select
Perform Functions
Range("A1").Select

I put the last line in so that it deslects the range simply by selecting another cell, but what I actually need is something like:

Range("A1:A20).Select
Perform Functions
Range("A1:20").Deselect

But there is no deselect, or unselect, function. So how do I just unselect the range without changing anything else?

Cheers
-Rob

[TheRobster]

nm I put this in the first post.

[royUK]

Hope this answers your questions

1] Dim a decimal as Double
2]TextBox1.Value="" or
TextBox1.Value=vbNullString should both clear TextBox1
3]Most times it is not necessary to select a Range before performing an action eg
Range("A1").Value = 1 will do without selecting A1 first

[opus]

On 1.) A Single could be enough, depends on the maximum value you want to use
On 2:.) The .Text property has to be cleared!

Textbox.Text=""

On 3.) There is (to my knowledge) no .Deselect. And you don't need it, if you are working with another range, just select it, this will "deselect" the former one.

[w0000992]

Can I bring question 3 back up again please? I'm having the same problem.

I want to copy some cells, including formatting, so I do the following:
Range("from").Select
Selection.Copy
Range("to").PasteSpecial

After this code, the "from" range has the flashing selection box around it. How do I get rid of this?

Selecting a new range doesn't help.
Range("A1").Select just positions to A1, but "from" is still flashing.

[westconn1]

this seems to work ok, but better to change like this instead of changing the selection

Range("from").Copy
Range("to").PasteSpecial

Edit: after running the code, which ever way, the to range was selected, then you can set the selection to where you want it for the user

[w0000992]

Thanks, I've changed by code to just use copy rather than select then copy, and I've added Range("A1").Select to stop pasted cells being selected.

But the "from" cells still have the flashing box around them, ready to be pasted again. (You know how Excel lets you copy a cell, then paste it as many times as you like? Interactively, I usually press <Escape> to get rid of the box around the copied cells, but what's the best way to get rid of it in vba?

[zaza]

Don't select anything!


Selecting is a clumsy way of manipulating ranges, and using the selection object will lead you to ruin in the long run. Anything that you can do with "Select", you can do just by using the named ranges instead.


zaza

[w0000992]

Don't select anything!

okay. I realise now that my problem isn't with selecting, it's with my copied "from" range still being in "copy from" mode. Do you know what I mean? That blinking box around the cells. How do I get rid of that after the paste?

[Webtest]

More than you ever wanted to know:

An active sheet ALWAYS has at least one cell (or other object) selected, UNLESS the sheet is protected and the 2 Select functions (Select Locked Cells and Select Unlocked Cells) are NOT checked.

You can, however, drive the Selected cell off the visible area of the sheet. Try this:

Option Explicit
Sub Macro1()
    'Drive the Selection to eternity
    ActiveSheet.Cells(65536, 256).Select
    'Scroll the window back to the top left corner (Row 1, Column 1)
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
End Sub

Sometimes these tricks come in handy! ... but not very often ...

As everyone else has stated, you should almost never need to select any cell for any operation.

[zaza]

Incidentally, unless you particularly need the PasteSpecial method, why not just use the Copy method to do it all:

CopyFromRange.Copy (CopyToRange)

zaza

[w0000992]

This still left my copied cells in "copy mode".
ActiveSheet.Cells(65536, 256).Copy solves my problem.

But this doesn't seem like an ideal solution does it.

What do you guys do after pasting a copied cell? Do you hit <Escape> to reset?

Is it common to copy cells around in vba? I'm sure there's a really obvious solution I'm missing here??

[westconn1]

when i stepped through the code i had that issue with the copied cells, but when i let the code run right through it did not occur
a brute force method to remove the highlight from the copy range is to use sendkeys

Range("from").Copy
Range("to").PasteSpecial
SendKeys "{esc}"

Edit:
better still try this

Range("from").Copy Range("to")

[DKenny]

This still left my copied cells in "copy mode".

To turn off the "copy mode", add the following code after you have pasted.

Application.CutCopyMode = False

[w0000992]

Cheers. I think I might have wasted everybodys time by trying to fix this before going on with next task. I think when I work with other cells, Excel will realise the copy range is no longer valid and remove the box. I've had this go away and come back as I've been making changes, so I think you've provided enough help for me to get this working.
Thanks for all your help.

[Webtest]

If anybody, especially you, learned anything, nobody's time was wasted.

[muunkky]

The command you are looking for is

Application.CutCopyMode = False

this just takes away the flashing boarder on the cell you are copying from.