Excel Macro for Beginners

Thursday, November 20, 2008

Hitung Red Font

---------- Forwarded message ----------
From: Safri Ishak <safri.ishak@gmail.com>
Date: Wed, Nov 12, 2008 at 6:30 PM
Subject: Re: ]] XL-mania [[ Hitung red Font
To: XL-mania@yahoogroups.com

Dear John,

if you dare :) to use macro, please download and try the attached excel file.

siapa takut .... he he.

--
Thank you and regards,
Safri
www.tb512.com my virtual home
www.tebetbarat.com Tebet Business Directory consists of addresses and phone numbers of favorite restaurants, traditional markets, hotels, offices, schools, super markets, malls, automotive, gardens, flowers, cakes, advertising, computers, salons, barber shops, cosmetics, banks, apartments etc.
Originally it was compiled for personal purposes and then published to the internet as a gateway to search business directory and websites in Tebet and surrounding area.

2008/11/12 John Robert May <jrobertm@abadiadimulia.com>

Dear XL Mania,

Minta tolon seperti kasus dibawah ini,

Jakarta

23

Semarang

16

Surabaya

22

Bali

11

Makasar

19

Jumlah Angka "Red Font"

2

( Note : didapat dari Conditional Formating)

Tanya : Bagaimana rumusnya pada Cell D10, supaya dapat menghitung jumlah angka yg ber - Red Font pada Range D2 - D6

Help me please

--
Thank you and regards,
Safri
www.tb512.com my virtual home
www.tebetbarat.com Tebet Business Directory consists of addresses and phone numbers of favorite restaurants, traditional markets, hotels, offices, schools, super markets, malls, automotive, gardens, flowers, cakes, advertising, computers, salons, barber shops, cosmetics, banks, apartments etc.
Originally it was compiled for personal purposes and then published to the internet as a gateway to search business directory and websites in Tebet and surrounding area.

Lookup Gambar masuk Userform

---------- Forwarded message ----------
From: Safri Ishak <safri.ishak@gmail.com>
Date: Wed, Nov 19, 2008 at 3:05 PM
Subject: Re: ]] XL-mania [[ lookup gambar masuk userform
To: XL-mania@yahoogroups.com

mas rizaldi,
saya coba buat makro seperti the attached excel file
silahkan didownload dan dicoba
janan lupa photo didownload ke dalam folder yang sama dengan makro
mudah2an berkenan

loadpicture saya contek dari jawaban ibu guru siti ....... he he.

--
Thank you and regards,
Safri
www.tb512.com my virtual home
www.tebetbarat.com Tebet Business Directory consists of addresses and phone numbers of favorite restaurants, traditional markets, hotels, offices, schools, super markets, malls, automotive, gardens, flowers, cakes, advertising, computers, salons, barber shops, cosmetics, banks, apartments etc.
Originally it was compiled for personal purposes and then published to the internet as a gateway to search business directory and websites in Tebet and surrounding area.

2008/11/19 rizaldi <Rizaldi.DjunaediAlKaaf@pajak.go.id>

TERIMA KASIH atas jawabannya mbk CTV

(saya ga ngerti koding babar blas hehe)

Tapi ini masih "belum sesuai keinginan/maksud" saya hehehe

Soalnya file picture yang saya punya itu bbuuaaanyak,,,yang saya ingin kan semacam "search" yang saya ketik or klik (septi pada pertanyaan saya pada attachment saya) lalu di preview melalui user form itu,,,

Maksut dan tujuan saya,,, kan di excel itu ceritanya ada banyak data2 misal data Wajib Pajak yang tentunya memiliki NPWP,,,nah jadi maksut saya ketika saya mengetik NPWP/mengKlik NPWP dari suatu WP, saya dapat memPREVIEW foto WP tersebut misalnya dengan menyajikan data2 WP tersebut dalam excel,,,kenapa saya pake userform,,karena menurut saya dengan make user form itu jadi lebih flexible,,dapat digerak2an/digeser dll

Mohon bantuannya lagi…

(saya attach lagi ya pertanyaan saya..??)

Tengs

Regards,

From: XL-mania@yahoogroups.com [mailto:XL-mania@yahoogroups.com] On Behalf Of siti Vi
Sent: 19 Nopember 2008 8:54
To: XL-mania@yahoogroups.com
Subject: Re: ]] XL-mania [[ lookup gambar masuk userform

kalau dengan userforms, katanya,
memasukkan image ke object melalui coding adalah
dengan property Picture,
instruksinya
Object.Picture = LoadPicture(PathDanNamaFIleImage)

kalau di sheet tidak perlu pakai userform kan sudah ada object Picture
yg bisa diisi gambar melalui formula. seperti yg bang mods buat.

2008/11/17 rizaldi <Rizaldi.DjunaediAlKaaf@pajak.go.id>
>
> Assalamu'alaikum>
> Help ni buat para pakar excel,,pertanyaan saya ada pada attachment
> (mirip2 dengan lookup gambar pada "50 jurus rahasia excel")
>

__._,_.___

Recent Activity

102

New Members

Visit Your Group

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

All-Bran

Day 10 Club

on Yahoo! Groups

Feel better with fiber.

Special K Group

on Yahoo! Groups

Learn how others

are losing pounds.

.

__,_._,___

Thursday, October 9, 2008

01 Display Messag Box

My First Macro
Copy and paste the following statements to Visual Basic Editor:
Option Explicit
Sub TEST()
MsgBox "My First Macro"
End Sub

My First Macro

Exctracted from Excel Macro
Help.

Option Explicit Statement
Used at module level to force explicit declaration of all variables
in that module.
Syntax
Option Explicit
Remarks
If used, the Option Explicit statement must appear
in a module before any procedures.
When Option Explicit appears in a module,
you must explicitly declare all variables using the Dim, Private, Public,
ReDim, or Static
statements.
If you attempt to use an undeclared variable name, an error occurs
at compile time.
If you don't use the Option Explicit statement, all
undeclared variables are of Variant type unless the default type is
otherwise specified
with a Deftype statement.
Note Use Option Explicit to avoid incorrectly
typing the name of an existing variable or to avoid confusion in code
where the scope of
the variable is not clear.

Option Explicit Statement

Dengan menggunakan option ini, maka semua variable (akan kita diskusikan
lebih lanjut) harus di deklarasikan.

Option Explicit harus dibuat dan
diletakkan paling atas,
sebelum prosedur yang lain. Untuk menghindari kesalahan meng-edit
pada waktu menggunakan variable, sangat dianjurkan untuk memakai Option
Explicit.

Sub TEST() Statement
Declares the name, arguments, and code that form the
body of a Sub procedure.

Syntax

[Private | Public | Friend] [Static] Sub name [(arglist)]

[statements]

[Exit Sub]

[statements]

End Sub

Sub TEST() Statement

Digunakan untuk mendeklarasikan nama prosedur,
argumen dan perintah yang akan membentuk sebuah Sub prosedur. Semua
prosedur didalam Excel Macro dimulai dengan Sub statement dan diakhiri
dengan End Sub. Dalam contoh ini nama yang digunakan adalah TEST.

MsgBox Statement

Displays a message in a dialog box, waits for the user
to click a button, and returns an Integer indicating which button the
user clicked.

Syntax
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
The MsgBox function syntax has these named arguments:

prompt Required. String expression displayed as the message in the
dialog box. The maximum length of prompt is approximately 1024 characters,
depending on the width of the characters used. If prompt consists
of more than one line, you can separate the lines using a carriage
return character (Chr(13)), a linefeed character (Chr(10)), or carriage
return – linefeed character combination (Chr(13) & Chr(10))
between each line.

buttons Optional. Numeric expression that is the sum of values specifying
the number and type of buttons to display, the icon style to use, the
identity of the default button, and the modality of the message box.
If omitted, the default value for buttons is 0.

title Optional. String expression displayed in the title bar of the
dialog box. If you omit title, the application name is placed in the
title bar.

helpfile Optional. String expression that identifies the Help file
to use to provide context-sensitive Help for the dialog box. If helpfile
is provided, context must also be provided.

context Optional. Numeric expression that is the Help context number
assigned to the appropriate Help topic by the Help author. If context
is provided, helpfile must also be provided.

MsgBox "My First Macro" Statement

Digunakan untuk menampilkan kata-kata My First
Macro di layar monitor.

Untuk mencoba macro ini,
silahkan tekan F5 dari Macro Editor, click RUN, untuk mengakhiri macro click
OK.

Click
icon untuk melihat gambar dengan ukuran yang lebih besar.

04 Edit Macro

Edit Macro

Open Excel, On Excel worksheet, click Tools, click Macro, click Visual
Basic Editor.

On Visual Basic Editor, click Insert, click Module.

Wednesday, October 8, 2008

02 Enable Macro

Enable Macro

to run in Excel Workbook

Open Excel, click Tools, click Macro, click Security, select
Medium and click OK. Close Excel.

Note: we need to do it only once.

Subscribe to: Posts (Atom)

macro statements

Exctracted from Excel Macro Help.

Option Explicit Statement
Used at module level to force explicit declaration of all variables in that module.
Syntax
Option Explicit
Remarks
If used, the Option Explicit statement must appear in a module before any procedures.
When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.
If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.
Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.

Option Explicit Statement
Dengan menggunakan option ini, maka semua variable (akan kita diskusikan lebih lanjut) harus di deklarasikan.
Option Explicit harus dibuat dan diletakkan paling atas, sebelum prosedur yang lain. Untuk menghindari kesalahan meng-edit pada waktu menggunakan variable, sangat dianjurkan untuk memakai Option Explicit.

Dim Statement
Declares variables and allocates storage space.
Syntax
Dim [WithEvents] varname[([subscripts])] [As [New] type] [, [WithEvents] varname[([subscripts])] [As [New] type]] . . .The Dim statement syntax has these parts:

Part Description
WithEvents Optional. Keyword that specifies that varname is an object variable used to respond to events triggered by an ActiveX object. WithEvents is valid only in class modules. You can declare as many individual variables as you like using WithEvents, but you can't create arrays with WithEvents. You can't use New with WithEvents.
varname Required. Name of the variable; follows standard variable naming conventions.
subscripts Optional. Dimensions of an array variable; up to 60 multiple dimensions may be declared. The subscripts argument uses the following syntax:
[lower To] upper [, [lower To] upper] . . .

When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.

New Optional. Keyword that enables implicit creation of an object. If you use New when declaring the object variable, a new instance of the object is created on first reference to it, so you don't have to use the Set statement to assign the object reference. The New keyword can't be used to declare variables of any intrinsic data type, can't be used to declare instances of dependent objects, and can’t be used with WithEvents.
type Optional. Data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, a user-defined type, or an object type. Use a separate As type clause for each variable you declare.

Remarks

Variables declared with Dim at the module level are available to all procedures within the module. At the procedure level, variables are available only within the procedure.

Use the Dim statement at module or procedure level to declare the data type of a variable. For example, the following statement declares a variable as an Integer.

Dim NumberOfEmployees As Integer

Also use a Dim statement to declare the object type of a variable. The following declares a variable for a new instance of a worksheet.

Dim X As New Worksheet

If the New keyword is not used when declaring an object variable, the variable that refers to the object must be assigned an existing object using the Set statement before it can be used. Until it is assigned an object, the declared object variable has the special value Nothing, which indicates that it doesn't refer to any particular instance of an object.

You can also use the Dim statement with empty parentheses to declare a dynamic array. After declaring a dynamic array, use the ReDim statement within a procedure to define the number of dimensions and elements in the array. If you try to redeclare a dimension for an array variable whose size was explicitly specified in a Private, Public, or Dim statement, an error occurs.

If you don't specify a data type or object type, and there is no Deftype statement in the module, the variable is Variant by default.

When variables are initialized, a numeric variable is initialized to 0, a variable-length string is initialized to a zero-length string (""), and a fixed-length string is filled with zeros. Variant variables are initialized to Empty. Each element of a user-defined type variable is initialized as if it were a separate variable.

Note When you use the Dim statement in a procedure, you generally put the Dim statement at the beginning of the procedure.

Sub TEST() Statement
Declares the name, arguments, and code that form the body of a Sub procedure.
Syntax

[Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]

End Sub

Sub TEST() Statement
Digunakan untuk mendeklarasikan nama prosedur, argumen dan perintah yang akan membentuk sebuah Sub prosedur. Semua prosedur didalam Excel Macro dimulai dengan Sub statement dan diakhiri dengan End Sub. Dalam contoh ini nama yang digunakan adalah TEST.

LTrim, RTrim, and Trim Functions
Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).
Syntax
LTrim(string)
RTrim(string)
Trim(string)

The required string argument is any valid string expression. If string contains Null, Null is returned.

Replace Function
Description
Returns a string in which a specified substring has been replaced with another substring a specified number of times.
Syntax
Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing substring to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted, the default value is –1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption –1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.

Return Values

Replace returns the following values:

If Replace returns
expression is zero-length Zero-length string ("")
expression is Null An error.
find is zero-length Copy of expression.
replace is zero-length Copy of expression with all occurences of find removed.
start > Len(expression) Zero-length string.
count is 0 Copy of expression.

Tebet Business Directory Alamat Usaha Kita

Free Posting IKLAN GRATIS, send your name, address, telephone, email id, website and brief description of your business to AdminTebetbarat.com

More information about www.TB512.com Click HERE.

Excel Macro for Beginners

TB512 Excel VBA

adsense

adsense links