In order to type the current date in a Microsoft Excel Cell, one must do the following:
1. Left Click on the cell
2. Press Ctrl + ;
When you write your own macros, a very important thing is to know exactly where your worksheet starts and where it ends. Here is a helpful macro that can be used in your own macros and does exactly that :
Sub DetermineUsedRange(ByRef theRng As Range)
Dim FirstRow As Integer, FirstCol As Integer, _
LastRow As Integer, LastCol As Integer
On Error GoTo handleError
FirstRow = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol))
handleError:
End Sub
The following macro uses the above code and displays in message boxes the used Range, the last used column and the last used row:
Sub CallDetermineUsedRange()
On Error Resume Next
Dim usedRng As Range
DetermineUsedRange usedRng
MsgBox usedRng.Address
MsgBox usedRng.Columns(usedRng.Columns.Count).Column
MsgBox usedRng.Rows(usedRng.Rows.Count).Row
End Sub
When programming in VBA, have you ever tried to construct a string which included double quotes?
If, yes and if you failed here is how you do it :
Let’s say you have the following code:
dim a as String
a="This is a test"
If you actually want variable "a" to contain the phrase This is a test, you don’t need anything else. But if you want variable "a" to contain the phrase "This is a test" in double quotes you, have to use concatenation and do the following:
a = char(34) & "This is a test" & char(34)