C Series Macro Examples

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

Sub FunctionDemo()

    Set myRange = Worksheets("Sheet1").Range("A1:B10")

    ans = Application.WorksheetFunction.Max(myRange)

    MsgBox ans

End Sub

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.

Cells Method

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.

Sub ProperCaseDemo()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = strconv(cell.text,vbProperCase)
End If
End Sub

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

End Sub

October 9, 2011

0 Responses on C Series Macro Examples"

Leave a Message

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com