Some times we need to put outlines around the cells. Think you have adjacent cells which contains data. So if you put outline to those cells it will clearly separate the data. And it also gives a nice and neat look to your spread sheet. Below code will put outline around each and every cell of range "C3" to "C5".
|
dim WR as worksheet set WR=worksheets("Sheet1") WR.Range(Cells(3, 3), Cells(3, 5)).Borders.LineStyle = xlContinuous |
After running above code your spread sheet will look look below.
We often have to deal with dates when we develop programs for real life situations. Because time has vital place in real life. So when we develop programs we often need to add intervals to the dates. We can use "DateAdd" in those cases.
Here below is a explanation of how to use a "DateAdd". We can use this not only to add dates but also to add weeks, months etc. So it will be very helpful when develop programs which are connected with dates.
| Result = DateAdd("d", 1, CDate(Range("A1"))) |
There should be valid date in A1. It will increment that date by a single day.
"d" means we are adding a day.
Below symbols can be used to add other intervals.
s - Second
n - Minute
h - Hour
q - Quarter
m - Month
y - Day of year
yyyy - Year
w - Weekday
ww - Week
This method can be used to substract dates as well. Below will substract 5 days.
| Result = DateAdd("d", -5, CDate(Range("A1"))) |
I think you all know about the "Sum" function in excel sheets. If you want to get the sum of values of cells from C5 to C246, you can simply write the formula "=Sum(C5:C246)" at the cell where you want the sum value to be appear.
You can use this function in VBA as well. Then you can get "Sum" value very easily.
Below is the code for that
|
Range("C247").Value = Application.Sum(Range("C5:C246"))
|
Sum value will appear at cell "C247"
Some times there are situations you need to use column numbers instead of column names when developing programs. You can use below code at those circumstances.
| Range("C247").Value = Application.Sum(Range(Cells(5, 3), Cells(246, 3))) |
please note that above two codes do the same thing. So they gives same values.
When developing VB programs for excel sheets, some times we need to track certain columns, rows or cells.
For an example think you have to do calculation for each and every cell in a excel sheet, only if there column heading is bold.
So below code can be used to check whether cell "A1" is bold or not
If DR.Range("A1").Font.Bold = True Then Do the calculation End If |
dim WS as worksheet
Set WS=worksheets("sheet1")
'find last column
Lastcol =WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
There are 16384 columns in excel 2010. Column title of last column is "XFD"
This is how I found it by VBA code.
I wrote below code.
And run it in a worksheet. Then I got below error message.
Clicked OK and went to end of columns using scroll bar. Found the result
These two colors give professional look to your excel sheet.
Range("D2").Interior.color = RGB(245, 245, 245) 'white smoke
Range("D3").Interior.color = RGB(220, 220, 220) 'gainsboro
white color can obtained by below RGB value
Range("D4").Interior.color = RGB(255,255,255) 'white color
Black color can be obtained by below RGB value
Range("D4").Interior.color = RGB(0,0,0) 'black color