Excel Crazy

C Series Macro Examples

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

<span style="font-size: small;"> Sub FunctionDemo()</span>

<span style="font-size: small;">    Set myRange = Worksheets("Sheet1").Range("A1:B10")</span>

<span style="font-size: small;">    ans = Application.WorksheetFunction.Max(myRange)</span>

<span style="font-size: small;">    MsgBox ans</span>

<span style="font-size: small;">End Sub</span>

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

February 18, 2012

0 responses on "C Series Macro Examples"

Leave a Message

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

top
 

© LOKESH PALIWAL 1987 - 2018

LOKESH@EXCELCRAZY[DOT]COM | 8826547882

Reporting Solutions | Data Consulting | Analytics Training

CONTACT US
close slider

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Message for us...

Skip to toolbar