C Series Macro Examples

Share Now


Calculate a Specific Cell

Excel will calculate that worksheet. When applied to a specific range, as in:

Worksheet(1).Rows(2).Calculate

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..

Worksheets(1).calculate

 

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
Next
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



Share Now
October 9, 2011

0 responses on "C Series Macro Examples"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy