A Few VBA for Excel Questions
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:
Code:
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:
Code:
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? :confused:
Cheers
-Rob
Re: A Few VBA for Excel Questions
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.
Re: A Few VBA for Excel Questions
this seems to work ok, but better to change like this instead of changing the selection
VB Code:
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
Re: A Few VBA for Excel Questions
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?
Re: A Few VBA for Excel Questions
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
Re: A Few VBA for Excel Questions
Quote:
Originally Posted by zaza
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?
Re: A Few VBA for Excel Questions
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:
Code:
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.
Re: A Few VBA for Excel Questions
Incidentally, unless you particularly need the PasteSpecial method, why not just use the Copy method to do it all:
VB Code:
CopyFromRange.Copy (CopyToRange)
zaza
Re: A Few VBA for Excel Questions
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??
Re: A Few VBA for Excel Questions
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
VB Code:
Range("from").Copy
Range("to").PasteSpecial
SendKeys "{esc}"
Edit:
better still try this
VB Code:
Range("from").Copy Range("to")
Re: A Few VBA for Excel Questions
Quote:
This still left my copied cells in "copy mode".
To turn off the "copy mode", add the following code after you have pasted.
VB Code:
Application.CutCopyMode = False
Re: A Few VBA for Excel Questions
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.
Re: A Few VBA for Excel Questions
If anybody, especially you, learned anything, nobody's time was wasted.
Re: A Few VBA for Excel Questions
The command you are looking for is
Application.CutCopyMode = False
this just takes away the flashing boarder on the cell you are copying from. :wave: