Calculate a Specific Cell
Excel will calculate that worksheet. When applied to a specific range, as in:
With the above syntax, Excel VBA will calculate only row 2 in worksheet 1. F you want to calculate all the formula in worksheet1 then insert the following code..
Calling a Worksheet Function from Visual Basic
In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object. The following procedure uses the Max worksheet function to determine the largest value in a range of cells
Set myRange = Worksheets("Sheet1").Range("A1:B10")
ans = Application.WorksheetFunction.Max(myRange)
The range A1:B10 on Sheet1 is Set as myRange. Another variable,
ans, is assigned the result of applying the Max function to
myRange which then show up in a message box when you execute the procedure.
To enter the the value 100 into Range(“B2”), the code below will do the trick…
ActiveSheet.Cells(2,2).Value = 100
We can also reference a specific cell in a Range object by using the code below…Here the value 100 is enter into Range(“C4”)
Set MyRange = Range(“C3:C10”)
MyRange.Cells(2).Value = 100
Change text to proper case
To change text in a selected range to proper case use the code below.
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = strconv(cell.text,vbProperCase)
Change the name of a Worksheet
You can change the name of a worksheet by using the Name property of the Worksheet object. The macro below will show you how.
Sub ChangeNameDemo ( )
Dim wsName As String
wsName = InputBox(“Enter a new worksheet name”)
ActiveSheet.Name = wsName