SEO , Top Keyword Research 2015

Google's Hummingbird update created a lot of anxiety, but ultimately, it could be a good thing for the industry, because it frees us from the tyranny of competing for a limited number of top keywords. Essentially, the role of the Hummingbird algorithm is to better answer those longer-tail queries users are typing in Google. If your pages are optimized for these more conversational queries, you have a better chance of top rankings. Try a new, niche-based approach to keywords, which allows you to double or even triple the list of profitable keywords in your SEO arsenal.

This article explains the four steps for doing keyword research the modern way, using SEO PowerSuite or other tools.
1. Ideas:

Most search marketers simply think of the main keywords related to their businesses, plop them into a tool like Google Keyword Planner, and then run with the keyword list it delivers. However, search habits vary widely: Searchers may use hundreds of different word combinations to describe the same thing, so this tactic omits hundreds of potentially profitable keywords.

Instead, try a more creative, ideas-based approach to your research. For example, if you hear the phrase office furniture, some associations you may have are chairs, tables, equipment, cubicles, or office design.
Related articles

    5 SEO Strategies That Will Still Work in 2015
    Navigating Secure Search: From Keywords to Content [BrightEdge Share 14]
    How to Win in Organic Search: Tips from Google, Bing and Brands
    How to do SEO for Local Business, WITHOUT Content Marketing
    The 3Ps of Content Measurement: Page Rank, Traffic & Engagement

But none of those associations will show up in Google Keyword Planner, because it shows you only the most closely related search terms. To get ideas based on these niche associations, first use your brain: Come up with as many associations that you can. Check competitors' sites and blogs. You'll be able to find more keyword ideas and also spot if they target just the key phrases from the Google Keyword Planner or if they have a smart keyword strategy.

Next, turn to multiple research methods to come up with still more niche keyword ideas:

    Related searches in Google, Bing, and Yahoo
    Google auto-complete
    Yahoo Search Assist
    Competition Research harvests keywords from your top-ranking competitors' meta tags
    SEMRush provides keywords from its unique keyword database
    Word Mixer shuffles the keywords you enter to build more variants of phrases
    SEO PowerSuite's Rank Tracker automates the search-engine suggestion process

Other good sources for keyword ideas include forum discussions, dictionaries and the thesaurus, and Wikipedia topic categories and articles related to your topic. The more research methods you use, the better. Each method will generate new suggestions.
2. Words:

Now it's time to pick the keywords that are truly relevant. Go through the list and remove the terms that don't seem very relevant to your product or service – even if a keyword tool said they were. Effective as they are, automated keyword research tools are not humans, so the results need to be evaluated by you.

Why not just use the Google Keyword Planner? Certainly, it's quick and easy, but it can be misleading. For example, the average monthly searches metric is just a rough estimate, not an accurate indicator of search volume. As well, search volume can vary widely from month to month. For example, seasonal keywords like "Christmas gifts" may get 350,000 searches in December and 100 in June. Meanwhile, the competition metric reflects the number of advertisers bidding on that keyword; if you're doing organic SEO, this metric can be pretty useless.

Instead, focus on longer, less popular keyword phrases. Those long-tail phrases don't receive huge search volumes, but they are less competitive, so it's easier for you to squeeze into the top 10. They tend to be more targeted and therefore better-converting. As a bonus, they already include the competitive short-tail words, so when you optimize for long-tail, you also optimize for the shorter, "head" words.

If you're looking for keyword phrases that will convert, choose words that signal commercial intent, rather than a hunt for information. Look for quick-sale words including buy, coupon, discount, deal, cheap, or shipping. Product-description words that people use to find out more about the products they are already interested in include review, best, and top 10, as well as product categories, specific products, and brand names.
3. Analysis:

Now that you've developed a solid list of potential keywords and phrases, it's time to do some analysis. Instead of relying on the old search-volume metric, use the more robust keyword effectiveness index (KEI) and Keyword difficulty score to see how hard it’ll be to rank for a keyword. These metrics take several niche-based factors into account, thus giving you an opportunity to prioritize your keywords based on their difficulty.

Additionally, you can detect keywords with higher commercial intent by looking at their estimated cost-per-click bids. Some keywords cost multiples more compared to other related ones. This means that more people compete for these words in Google AdWords, since they bring them more conversions and sales.
4. Competition:

To finalize your list of keywords, you need to understand how hard it will be to outrank competitors for each of them. This is the best way to evaluate your true opportunities.

Here are the factors that you need to check for within the top-ranking pages in the search results for your main keywords in Google:

    Backlinks – Identify the backlinks to the search landing page. The search engines consider backlinks as votes for a certain page, so the more links your competitor has, the harder it may be to outrank this site.
    On-page SEO – Evaluate the quality of on-page optimization of the competing pages by scanning their titles, descriptions, content, and internal link anchors.
    Content – Users love unique and useful content – and so does Google. Inspect competitors' page for quality content.
    Domain authority – Finally, check the age of the competing domains, their Alexa rank, and their popularity in social media.

If you are just beginning your modern SEO strategy, you may want to remove all keywords that your competitors have ranked for, even if the KEI is good. With millions of sites competing for rankings for a given keyword, it will take you a while to rank high for the term. Also discard keywords and phrases with the worst KEI; these terms have both intense competition and low search volume, so optimizing for them is not worthwhile at this point.

Out of the remaining keywords, choose the ones with the best KEI. You should now have a list of lucrative, low-competition keywords that will bring you lots of conversions.

If you're struggling with the research, let Rank Tracker easily find these keywords for you. For more information on how to use Rank Tracker to find the best-performing keywords and phrases, read our Ultimate Guide to Keyword Research with Rank Tracker. Happy hunting


Requoted by

SISINDOTEK – IT Training & Solution Provider

Office 1 : Jl. Pelajar Pejuang 45 No.23 , Lt.2 Bandung – Jawa Barat
Office 2 : Jl. Sukasenang VI-6B Bandung – Jawa Barat 40124
Tel. 022-71242266, SMS. 0812.8733.1966
info@sisindotek.com , YM. sisindotek
Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Ms. Excel , INDIRECT function for dynamic range

INDIRECT Formula used to Total a Dynamic Range of Values

This example is based on the data shown in the image above.

The SUM – INDIRECT formula created by using the tutorial steps below is:

=SUM(INDIRECT("D" &E1& ":D" &E2))

In this formula, the nested INDIRECT function's argument contains references to cells E1 and E2. The numbers in those cells, 1 and 4, when combined with the rest of INDIRECT's argument, form the cell references D1 and D4.

As a result, the range of numbers totaled by the SUM function is the data contained in the range of cells D1 to D4 – which is 50.

By changing the numbers located in cells E1 and E2; however, the range to be totaled can be easily changed.

This example will first use the above formula to total the data in cells D1:D4 and then change the summed range to D3:D6 without editing the formula in cell F1.

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

MS Excel, Create Running total with SUM

If you’re using worksheet formulas, instead of a pivot table, there’s nothing built in that will automatically create a running total for you. Fortunately, with a simple SUM formula, you can calculate the running total in each row, to see how your bank account is doing.

Use the SUM Function

In this example, there are withdrawal and deposit amounts in cells C2:C6. We could use the SUM function to total the amounts in column C, and that would give us the current total.

sumcolumn

Instead, we’ll use the SUM function in each row, to calculate the running total. We’ll be able to see the total after each withdrawal and deposit.

The formula in cell D2 is: =SUM(C$2:C2)

sumrunningtotal01

That $ sign locks the reference to row 2, at the start of the sum, so it doesn’t change when the formula is copied down to cell D6.

The ending cell – C2 – is a relative reference, so the ending point changes for each row. For example, in cell D6, the formula shows C6 as the ending cell for the SUM formula.

sumrunningtotal02

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

MS Excel, Cara membuat saldo berjalan

Berikut adalah cara menghitung saldo berjalan  di Microsoft Excel :

  1. Langkah pertama anda membuat microsoft excel kemudian pilih worksheet yang tersedia atau anda dapat mengisikan data di workbook.
  2. Kemudian anda dapat menambahkan garis sehingga terbentuk tabel pada laporan anda. Caranya anda dapat blok semua data kemudian klik garis untuk membuat tabel.
  3. Selanjutnya pada kolom saldo anda dapat menambahkan rumus, yaitu misalnya datanya  dengan =IF(OR(D2<>0;E2<>0);SUM($D$2:D2)-SUM($E$2:E2);0) . Dengan keterangan kolom debet yang terdapat pada sel D2, kemudian kredit pada sel E2. Dan masukkan kode tersebut di sel F2. Pada dasarnya Anda dapat menyesuaikan kolom debet dan kredit dengan tabel sehingga menganti kode sesuai dengan data yang terdapat di tabel anda. Sedangkan bagi anda yang akan menambah penjumlahan saldo maka anda dapat mengarahkan kursor ke arah bawah kemudian tunggu hingga berubah menjadi +, selebihnya click and drag.

Itulahh cara yang dapat anda lakukan untuk menghitung saldo berjalan di Microsoft Excel sehingga membantu proses kerja anda lebih mudah.

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Freelance personal trainer jakarta, bandung, surabaya, semarang, bali, singapore

Hery Purnama +62.81.223344.505 , pinbb : 7dc633aa ,freelance personal trainer for management, marketing, IT, excel vba macro, android phonegap, Sencha extjs, code igniter, yii framework, google map api, google sketchup 3d, php ajax jquery, uml, rdbms concept, project management, ms project, ms access, excel for accounting, ITIL , oracle dba, sql server, VB.net , asp.net, sms gateway, marketing strategy. Pengajar freelance , 15 years experience and certified trainer Call +62.81.223344.506 for invitation or goto http://freelance-it-trainer.blogspot.com

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA , Clear Table Formatting

Using the code on Worksheets with only one table you could just use the index number instead of the name:

ActiveSheet.ListObjects(1).TableStyle = ""

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Istilah akuntansi dalam Bahasa Inggris

Buat Anda yang sedang buat aplikasi Akuntansi , muingkin istilah berikut membantu

Aktiva = asset
Aktiva bersih = net asset
Aktiva lancar = current assets
Aktiva tetap = fixed assets
Aktiva tetap berwujud = tangible fixed assets
Aktiva tetap tidak berwujud = intangible fixed assets
Akumulasi = accumulation
Akumulasi penyusutan = accumulated depreciation
Akumulasi penyusutan bangunan = accumulated depreciation of building
Akumulasi penyusutan kendaraan = accumulated depreciation of vehicle
Akumulasi penyusutan mesin = accumulated depreciation of machinary
Akumulasi penyusutan peralatan = accumulated depreciation of equipmen
Akun = account
Akuntansi = accounting
Akuntansi anggaran = budgeting
Akuntansi biaya = cost accounting
Akuntansi kemasyarakatan = social accounting
Akuntansi keuangan = financial accounting
Akuntansi manajemen = management accounting
Akuntansi pemerikasaan = auditing
Akuntansi pemerintahan = government accounting
Akuntansi perpajakan = tax accounting
Arus kas = cash flow
Asuransi bayar dimuka = prepaid insurance
B
Bangunan = building
Barang dagangan = merchandise
Barang siap jual = goods available for sale
Beban = expense
Beban administrasi dan umum = administrative and general expense
Beban asuransi = insurance expense
Beban bunga = interest expense
Beban dibayar dimuka = prepaid expense
Beban gaji = salaries expense
Beban iklan = advertise expense
Beban komisi = commission expense
Beban luar usaha = non operating expense
Beban pajak = 
tax expense
Beban penjualan = selling expense
Beban penyusutan = depreciation expense
Beban penyusutan kendaraan = depreciation expense of vehicle
Beban penyusutan peralatan = depreciation expense of equipment
Beban perlengkapan = supplies expense
Beban sewa = rent expense
Beban usaha = operating expense
Beban yang masih harus dibayar = accrued expensed
Biaya angkut pembelian = freight in/transportation in/carriage inward
Biaya angkut penjualan = freight out/transportation out/carriage outward
Bukti pembelian = purchase invoice
Bukti penjualan = sales invoice
Bukti-bukti dokumen = source of document
Buku besar = ledger
Buku besar pembantu piutang = account receivable subsidiary ledger
Buku besar pembantu utang = account payable subsidiary ledger
Buku besar tambahan/pembantu = subsidiary ledger
Buku besar umum = general ledger
Buku persediaan = stock ledger sheets


D
Debitur = debtor
Debet = debt

E
Efek/surat berharga = marketable securities

F
Faktur = invoice

H
Hak atas kekayaan = equities
Hak cipta = copyright
Hak perolehan = historical cost/at cost
Harga pokok penjualan = cost of goods sold

I
Iklan dibayar dimuka = prepaid advertising
Ikhtisar laba rugi = income summary
Investasi tambahan = additional investment

J
Jatuh tempo = maturity
Jurnal = Journal
Jurnal khusus = special journal
Jurnal koreksi = correction entries
Jurnal pembalik = reversing entries
Jurnal penerimaan kas = cash receipt journal
Jurnal pengeluaran kas = cash disbursement/cash payment journal
Jurnal penjualan = sales journal
Jurnal penutup = closing entries
Jurnal penyesuaian = adjustment entries
Jurnal umum = general entries

K
Kartu persediaan = stock card
Kartu piutang = debtors account
Kas di bank = cash in bank
Kas di tangan = cash on hand
Kekayaan = property
Kekayaan bersih = net worth
Kertas saham = worksheet
Keuntungan saham = dividend
Kewajiban = liabilities
Kewajiban jangka panjang = long term liabilities
Kewajiban lancar/jangka pendek = current liabilities
Konsep kesatuan usaha = business unit entity concept
Kredit = credit

L
Laba bersih = net income
Laba ditahan = retained earnings
Laba kotor = gross profit
Laba operasional = operating income
Laba penjualan aktiva = gain on sale of assets
Laba usaha = operating income
Laporan = report form
Laporan akuntansi = accounting statement
Laporan keuangan = financial statement
Laporan laba rugi = income statement

M
Merek dagang = trademark
Mesin = machinary
Modal = capital
Modal akhir periode = ending capital
Modal awal periode = beginning capital
Modal pemilik = owner's equity
Modal pinjaman = debt capital
Modal saham = capital stock

N
Nama akun = account title
Nama baik = goodwill
Neraca = balance sheet
Neraca saldo = trial balance
Neraca saldo setelah pentupan = post closing trial balance
Neraca saldo setelah penyesuaian = adjusted trial balance
Nilai buku = book value
Nilai jatuh tempo = maturity value
Nilai masa kini = current value
Nilai residu = residual value
Nota debet/kredit = debt/credit memo

O
Obligasi utang = bond payable

P
Pabrik = manufacturing
Pajak penghasilan = income tax
Pembelian = purchases
Pembelian bersih = net purchase
Pembukuan = book keeping
Pembukuan berpasangan = double entry book keeping
Penafsiran = interpeting
Pencatatan = recording
Pendapatan = income/revenue
Pendapatan bunga = interest income/revenue/earned
Pendapatan jasa = fees income
Pendapatan jasa diterima dimuka = unearned service revenue
Pendapatan komisi = commission revenue
Pendapatan luar usaha = non operating revenue
Pendapatan sewa = rent income/revenue
Pendapatan sewa diterima dimuka = unearned rent
Pendapatan usaha = operating revenue
Pendekatan neraca = balance sheet approach
Pengelompokan = classifying
Pengeluaran = expenditure
Pengendalian persediaan = stock control
Pengikhtisaran = summarizing
Pengukuran = measuring
Penjualan = sales
Penjualan bersih = net sales
Penjualan kredit = sales on credit/credit sales
Penjualan tunai = cash sales
Penyusutan = depreciation
Peralatan = equipment
Periode akuntansi = accounting period
Periode fiskal = fiscal period
Perlengkapan = supplies
Persamaan dasar akuntansi = accounting equation
Persediaan akhir barang dagangan = ending inventory/stock
Persediaan awal barang dagangan = beginning inventory/stock
Perusahaan dagang = commercial enterprise/trading company
Perusahaan jasa = service enterprise
Perusahaan perorangan = proprietorship/ownership
Piutang bunga = interest receivable
Piutang usaha = account receivables
Pos-pos neraca = balance sheet items
Potongan dagang = trade discount
Potongan pembelian = purchase discount/discount received
Potongan penjualan = sales discount/discount allowed
Potongan tunai = cash discount
Prive = drawing/withdrawl

R
Retur pembelian = purchase return
Retur penjualan = sales return
Rugi bersih = net loss
Rugi operasional = operating loss
Rugi penjualan aktiva = loss on sale of assets

S
Saham = stock
Saldo akun = account balance
Saldo sisa = balance
Sewa dibayar dimuka = prepaid rent
Sistem akuntansi = accounting system
Sistem berkala/terus-menerus = perpetual system
Suku bunga = interest rast
Syarat pembayaran = credit term

T
Tanah = land
Tanda pemeriksaan = check mark
Tata buku berpasangan = double entry
Transaksi = transaction

U
Utang = debt
Utang bank = bank loan
Utang bunga = interest payable
Utang gaji = salaries payable
Utang hipotik = mortgage
Utang pajak = tax payable

W
Wesel bayar = notes payable
Wesel tagih = notes receivable

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

MS Access VBA how to open Excel File

Sometimes you need to open excel file from your ms access form, here is the code you may try..

Option Compare Database
Option Explicit

Sub OpenSpecific_xlFile()

    Dim oXL As Object
    Dim oExcel As Object
    Dim sFullPath As String
    Dim sPath As String
   
       
'   Buat Excel instance
    Set oXL = CreateObject("Excel.Application")
   
   
'  Handling control Property
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
   
       
'   Full path file yang akan dibuka
    On Error GoTo ErrHandle
    sFullPath = CurrentProject.Path & "FILECONTOH_INVOICE.xlsm"
   
   
'   buka file
    With oXL
        .Visible = True
        .Workbooks.Open (sFullPath)
    End With
   
   
ErrExit:
    Set oXL = Nothing
    Exit Sub
   
ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Sub

Thanks

Hery (Freelance IT Trainer 081223344506)

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA activeworkbook refreshall WAIT until finished

Sometimes you need the delay or waiting excel VBA code till all the workbook refreshing methode is finished

For i = 1 To ActiveWorkbook.Connections.Count
    ActiveWorkbook.Connections(i).ODBCConnection.BackgroundQuery = False 'for odbc
    'ActiveWorkbook.Connections(i).OLEDBonnection.BackgroundQuery = False 'for oledb
    'MsgBox ActiveWorkbook.Connections(i).OLEDBConnection.BackgroundQuery
Next
    ActiveWorkbook.RefreshAll
   

Thanks,

Regards

Hery ( Freelance Excel VBA Trainer 081223344506)

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Freelance trainer excel vba macro , bandung jakarta

Hery Purnama 081223344505 , freelance inhouse excel vba macro, android phonegap, google map api, google sketchup 3d, php ajax jquery, project management, ms project, ms access, excel for accounting, ITIL , oracle dba, sql server, VB.net , asp.net, sms gateway, marketing strategy. Pengajar freelance inhouse berpengalaman 15 tahun and certified trainer

Call 081.223344.506 for invitation or goto http://freelance-it-trainer.blogspot.com

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Freelance Certified IT Trainer Bandung

Freelance IT Trainer, Pengajar Komputer, Inhouse Trainer, Management Trainer , Hery Purnama

Hery Purnama 081.223344.506 , Trainer Jakarta, Bandung, Yogya, Indonesia, Freelance IT Trainer, Pengajar Komputer Lepas , Instruktur komputer, Inhouse Training Certified IT Trainer.

Cari Pengajar , Trainer IT / Komputer untuk inhouse training di perusahaan Anda , Hubungi Hery Purnama 081223344506 , Berpengalaman lebih dari 15 tahun sebagai IT Trainer & IT Consultant

Hery Trainer/ Pengajar Excel VBA , Android Phonegap, Google Map API, Google Sketchup3D , MS Project, ExtJS, Oracle, SQL Server, MySQL, MS. Access VBA, RDBMS Concept, PHP , Ajax JQUERY, Yii Framework, Code Igniter, PHP SMS Gateway, Photoshop, CorelDraw, UML, Facebook API Developer dll.. Berpengalaman 15 Tahun sebagai Trainer bersertifikasi CIW, OCA, ITILF, MCP, MOS

Training Topics :

  • Google Application Integration Toolbox  for web developer
    Building Integrated Map Website with PHP MySQL and Google Map API V.3
    Web Integration with Facebook API developer toolbox
    Web Development with CMS WordPress
    Web Development with PHP Yii Framework
    Web Development with PHP CodeIgniter
    Project Management Methodologies  With Microsoft Project 2010
    SEO and Internet Marketing
    RDBMS – Database Concept
    ITIL V.3 Foundation
    Building Database Application with Microsoft Access 2010
    Microsoft Access 2010 VBA Macro programming
    MYSQL Server DBA Fundamental
    Object Manipulation with Adobe Photoshop CS
    CorelDraw X6 for Marketing and Promotion
    Google Sketchup 3D for Building Interior
    Building Flash Animation with SWISHMAX
    Building PHP MySQL – AJAX Web Application with Adobe Dreamweaver CS
    Building  ASP.Net Application with IronSpeed Designer
    Building PHP MySQL Web Application with CodeCharge Studio
    Mobile Application Development for Android with Jquery Mobile, MySQL & Phonegap
    HTML 5 for Mobile Application Development
    Microsoft Office 2010 (WORD, EXCEL, POWERPOINT)
    Microsoft Excel Visual Basic for Application (VBA-Macro)
    Networking Essential
    Installing, Configuring and Administering Windows Server 2008
    Installing, Configuring and Administering UBUNTU
    Installing and Configuring Exchange Server 2008
    CISCO Router
    Application Programming with VB.NET
    Web and Application programming with ASP.NET
    Web and Application programming with PHP – MYSQL
    Building Rich Internet Application  with PHP and Sencha ExtJs
    Building web application with PHP MySQL & AJAX Jquery
    Building Event Scheduler System with PHP AJAX and SMS Gateway
    Computer for Secretary and Admin Staff (Networking  Essential, MS. Outlook, Document Security and sharing, MS. Office Integration, File Archive and data backup – Recovery, etc.)
    Kerio Winroute Firewall
    SQL Server 2008 Database Design & DBA Fundamental

My Clients:

  • PT. Sampoerna Tbk – Bandung
  • PT. Asuransi AXA Indonesia – Jakarta
  • Icon Plus (Icon PLN – jakarta)
  • Bank Ekonomi – Jakarta
  • PT PLN (Persero) PUSHARLIS UWP III
  • PT. Karenindo Citra Utama – Jakarta
  • ITENAS – Bandung
  • Universitas Bina Nusantara (BINUS – Jakarta)
  • Bank Jabar Banten (BJB)
  • AMDOCS – Jakarta
  • PT. Elnusa, Tbk.
  • PT. Sarana Multigriya Finansial / SMF (Persero)
  • PT. Pertamina Geothermal Energy (Thamrin – Jakarta)
  • PT. Indonesia Asahan Alumunium (INALUM- Medan)
  • UIN SUSKA – Riau
  • Rumah Sakit AWALBROS Bekasi
  • Universitas Andalas
  • Politeknik ACEH
  • Pusinfowas BPKP – Jakarta
  • Asuransi Takaful – Jakarta
  • PT. Freeport , Tbk – Tembagapura Papua
  • PT. Gerbang Sinergi Prima – Bandung
  • PT PLN (PERSERO) UIP TRANSMISI ISJ – jakarta
  • PT. CG Power System Indonesia – Bogor
  • PT. Pertamina (Persero) – Jakarta
  • Bank CIMB Niaga –  Jakarta
  • Ikatan Mahasiswa Teknik Metalurgi ITB – Bandung
  • PT. Pupuk Kijang – Cikampek
  • PT. Pertamina (Persero – Jakarta)
  • Bakrie Telecom Jakarta
  • SMKN 2 Garut
  • PT BES Kelapa Gading Jakarta
  • PT. Telkomsel – Jakarta
  • PT. Hutama Karya Persero (Bandung)
  • PT. Seascape Survey Indonesia
  • PT. Pupuk Kaltim
  • Dana Pensiun Pupuk Kaltim
  • PT. Sarinah (Persero)
  • PT. Weda Bay Nickel (Halmahera)
  • Taq-Taq Operating Company Ltd (Kurdhistan – Irak)
  • EMP MALACCA STRAIT SA – Jakarta
  • Bank BTPN Jakarta
  • PT. Sarana Multi Infrastruktur (Persero)
  • PT. Medco Energi – Jakarta
  • PT. ANTAM, Tbk.
  • Unversitas Islam Indonesia (Teknik Informatika)
  • Institut Teknologi Telkom Bandung
  • Politeknik Negeri Malang
  • PT. KPEI
  • DPPKAD Kabupaten Gresik
  • PT PJB UPHB
  • PT. Krakatau Steel


    Building Integrated Map Website with PHP MySQL and Google Map API V.3
    (Inhouse ICON PLN Jakarta)
    icon PLN inhouse sisindotek
    Inhouse Training ini membahas tentang bagaimana membuat peta digital interaktif dan dinamis dengan informasi yang koordinat / LatLong yang tersimpan di database lalu ditampilkan secara dinamis menggunakan Google MAP API V.3 , beberapa metode yang dipelajari seperti Single Marker, Multiple Marker, Polyline, Geofence, Geocode, Direction, Single Marker, Multiple marker Custom Marker , Polyline dan lainnya.
     
    Microsoft Excel Advance for Business, Accounting & Management (Bank BJB – 4 Angkatan)
    Bank BJB inhouse sisindotek
    Kegiatan inhouse training dengan Bank BJB sebanyak 4 angkatan dengan total peserta 26 orang membahas pemanfaat excel dengan tingkatan yang lebih tinggi berupa pemanfaatn formula, function baik yang sudah umum ataupun yang tidak umum untuk membuat kemudahaan dalam pengelolaan data buat kebutuhan bisnis, Akuntansi ataupun manajemen.
     
    Building Event Scheduller Application with PHP and SMS Gateway (PLN)
    Training ini kembali diikuti oleh klien exclusive group dari PT PLN (PERSERO) UIP TRANSMISI ISJ, training ini membahas cara
    membuat aplikasi Event Scheduller dengan PHP, MySQL dan SMS gateway sebagai notification systemnya
     
    Mobile Application development for Android using Jquery Mobile and Phonegap (Bakrie Telecom)
    Inhouse Training membahas pemanfaat framework JQM dan phonegap dipadukan dengan PHP Ajax untuk membuat aplikasi berbasis Android dengan mudah
     
    Project Management Methodologies with MS Project (Inhouse Training INALUM Medan)
    Inhouse Training Project Management with MS. Project bersama staff dari PT. Indonesia Asahan Alumunium (INALUM) , Training membahas konsep Project Management dan implementasinya ke dalam software MS. Project 2010, termasuk dalam pembahasan adalah Project Management Lifecycle, Cost, Budget dan metodologi seperti PERT, CPM, PDM dan juga Earned Value Concept (SV, CV, CPI, SPI, BCWS, BCWP, ACWP dll.
     
    Excel VBA Macro (Inhouse training PGE Indonesia)
    Training Excel VBA Macro Programming bersama staff dari PT. Pertamina Geothermal Energy – Jakarta, Training ini membahas pemograman
    di Excel untuk melakukan otomatisasi dan pengembangan kinerja excel untuk memenuhi kebutuhan yang lebih kompleks
     
    ITIL v3 Foundation (Exclusive Group)
    Training ITIL v.3 Foundation yang diselenggarakan di Hotel Bintang 4 (DeJava Hotel , PVJ Bandung) diikuti oleh 3 peserta dari staff PUSINFOWAS BPKP jakarta
    Training ini bertujuan memahami konsep dari pengelolaan IT berbasis layanan (IT Service Management) berdasarkan framework IT Infrastructure Library (ITIL) versi 3. Beserta tambahan skenario studi kasusnya dan implementasi menggunakan ITSM software simulation serta simulasi persiapan ujian ITIL
     
    Excel VBA Macro (Exclusive Group)
    Training Microsoft Excel Visual Basic for Application (VBA – Macro) bersama 7 peserta dari PT. Freeport , Tbk (Exclusive Group) yang terbagi atas 3 gelombang / Periode
    Training ini membahas Pengenalan dan pemanfaatan Macro dan pemograman VBA untuk menyempurnakan dan memaksimalkan penggunaan Microsoft Excel lebih diatas penggunaan standard pada umumnya. pengenalan objek Excel dan VBA memanipulasi objek, penggunaan control , active x , array, looping , membuat function merupakan beberapa materi yang diberikan dalam pelatihan ini. Dikarenakan paket exclusive group maka training ini juga khusus membahas kasus khusus PT Freeport untuk diterapkan di bagian planner PT Freeport sesuai kebutuhan pekerjaan.
     
    Building Integrated Map Website with PHP MySQL & Google MAP API V.3
    Training Building Integrated Map Website with PHP and Google Map API V.3 diikuti oleh peserta Exclusive Group dari PT PLN (PERSERO) UIP TRANSMISI ISJ
    Training ini membahas tentang bagaimana membuat peta digital interaktif dan dinamis dengan informasi yang koordinat / LatLong yang tersimpan di database lalu ditampilkan secara dinamis menggunakan Google MAP API V.3 , beberapa metode yang dipelajari seperti Single Marker, Multiple Marker, Polyline, Geofence, Geocode, Direction, Custom Marker dan lainnya.
     
    Inhouse Training Microsoft Excel VBA Macro
    Inhouse Training Microsoft Excel Visual Basic for Application (VBA – Macro) bersama 15 peserta dari Ikatan Mahasiswa Teknik Metalurgi ITB Bandung
    Training ini membahas Pengenalan dan pemanfaatan Macro dan pemograman VBA untuk menyempurnakan dan memaksimalkan penggunaan Microsoft Excel lebih diatas penggunaan standard pada umumnya. pengenalan objek Excel dan VBA memanipulasi objek, penggunaan control , active x , array, looping , membuat function merupakan beberapa materi yang diberikan dalam pelatihan ini.
     
    Exclusive Group Training Project Management Methodologies with MS. Project 2010 PT.ANTAM

    Pelatihan Project Management Methodologies with MS. Project 2010 bersama 6 peserta Exclusive Group dari PT. ANTAM, Tbk. (Aneka Tambang) Sulawesi Tenggara
    Training ini membahas konsep Project Management beserta metodologi yang diimplementasikan menggunakan MS. Project , pembahasan teori berupa Konsep CPM, PDM, Earned Value (BCWS, SV, CV, SPI dll) Juga dibahas untuk memberi bekal mendalam perihal Project Management.
    disertai pembahasan studi kasus
    Kurva S
Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Android JQuery Mobile , Introduction

 JQueryMobile (JQM)

(Mengenal Jquery Mobile Framework )

jQuery Mobile is built on top of the jQuery library, which makes it easy to learn if you already know jQuery.

It uses HTML5, CSS3, JavaScript and AJAX to accomplish its work for laying out pages with minimal scripting.
Why Use jQuery Mobile?

jQuery Mobile takes the “write less, do more” to a new level: It automatically design web pages with an attractive and “easy-to-use” look, that will work the same way on all mobile devices.

Note     Instead of writing one application for each mobile device or OS:

    Android and Blackberry is written in Java

    iOS is written in Objective C

    Windows Phone is written in C# and .net, etc.

jQuery Mobile solves this problem, as it only uses HTML, CSS and JavaScript, which is standard for all mobile web browsers!

Best Reading Experience

Even though jQuery Mobile works on all mobile devices, it may have some compatibility issues on desktop computers (due to limited CSS3 support).

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA, Prevent Case Sensitive with Ucase function

Ucase Function

 (Menangani case sensitif dengan function Ucase Excel VBA)

The Ucase function can compare text in a range of cells, allowing us to write a macro that compares text case-insensitively.

Here is one that displays a message box if it encounters any cell in A1:A10 of the active sheet containing any case variation of the word CAT.

The code.
To insert the code, press Alt/Option-F11, select Insert → Module, and paste the following:

Sub CompareText( )
Dim rCell As Range

    For Each rCell In Range(“A1:A10”)
    If UCase(rCell) = “CAT” Then
        MsgBox rCell.Address & ” has ” & rCell & ” in it”
    End If
    Next rCell
End Sub

Exit the VBE and return to Excel and save your workbook.

To run the macro, select Developer → Macros (pre-2007, Tools → Macro → Macros…), select CompareText from the list, and click Run. So if you had the word CAT (uppercase) in cells A1:A9 and the word cat (lowercase) in A10, the macro would display a message box for each instance of the word “cat,” regardless of what case it is entered in.

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA , Variable Tutorial

Variables in Excel VBA

(Mengenal dan menggunakan Variable di Excel VBA)

Integer    |   String    |   Double    |   Boolean

This tutorial teaches you how to declare, initialize and display a variable in Excel VBA. Letting Excel VBA know you are using a variable is called declaring a variable. Initializing simply means assigning a beginning (initial) value to a variable.

Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.

Integer

Integer variables are used to store whole numbers.
Dim x As Integer
x = 6
Range(“A1”).Value = x

Explanation: the first code line declares a variable with name x of type Integer. Next, we initialize x with value 6. Finally, we write the value of x to cell A1.

String

String variables are used to store text.

Code:
Dim book As String
book = “bible”
Range(“A1”).Value = book

Explanation: the first code line declares a variable with name book of type String. Next, we initialize book with the text bible. Always use apostrophes to initialize String variables. Finally, we write the text of the variable book to cell A1.

Double

A variable of type Double is more accurate than a variable of type Integer and can also store numbers after the comma.

Code:
Dim x As Integer
x = 5.5
MsgBox “value is ” & x

Result is Not Accurate Enough

But that is not the right value! We initialized the variable with value 5.5 and we get the value 6. What we need is a variable of type Double.

Code:
Dim x As Double
x = 5.5
MsgBox “value is ” & x

Note: Long variables have even larger capacity. Always use variables of the right type. As a result, errors are easier to find and your code will run faster.

Boolean

Use a Boolean variable to hold the value True or False.

Code:
Dim continue As Boolean
continue = True

If continue = True Then MsgBox “Boolean variables are cool”

Explanation: the first code line declares a variable with name continue of type Boolean. Next, we initialize continue with the value True. Finally, we use the Boolean variable to only display a MsgBox if the variable holds the value True.

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA , Range Object, Cells, Rows, Columns, Copy , count Tutorial

Range Object Refferences

(Mengenal Object Range di Excel VBA Macro)

Range Examples | Cells | Declare a Range Object | Select | Rows | Columns | Copy/Paste | Clear | Count
The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. This chapter gives an overview of the properties and methods of the Range object. Properties are something which an object has (they describe the object), while methods do something (they perform an action with an object).
Range Examples

Place a command button on your worksheet and add the following code line:
Range(“B3”).Value = 2

Excel VBA Range Example

Code:
Range(“A1:A4”).Value = 5

Range Example

Code:
Range(“A1:A2,B3:C4”).Value = 10

Cells

Instead of Range, you can also use Cells. Using Cells is particularly useful when you want to loop through ranges.

Code:
Cells(3, 2).Value = 2

Cells in Excel VBA

Explanation: Excel VBA enters the value 2 into the cell at the intersection of row 3 and column 2.

Code:
Range(Cells(1, 1), Cells(4, 1)).Value = 5

Declare a Range Object

You can declare a Range object by using the keywords Dim and Set.

Code:
Dim example As Range
Set example = Range(“A1:C4”)

example.Value = 8

Select

An important method of the Range object is the Select method. The Select method simply selects a range.

Code:
Dim example As Range
Set example = Range(“A1:C4”)

example.Select

Rows

The Rows property gives access to a specific row of a range.

Code:
Dim example As Range
Set example = Range(“A1:C4”)

example.Rows(3).Select

Columns

The Columns property gives access to a specific column of a range.

Code:
Dim example As Range
Set example = Range(“A1:C4”)

example.Columns(2).Select

Copy/Paste

The Copy and Paste method are used to copy a range and to paste it somewhere else on the worksheet.

Code:
Range(“A1:A2”).Select
Selection.Copy

Range(“C3”).Select
ActiveSheet.Paste

Although this is allowed in Excel VBA, it is much better to use the code line below which does exactly the same.
Range(“C3:C4”).Value = Range(“A1:A2”).Value
 

Clear

To clear the content of an Excel range, you can use the ClearContents method.
Range(“A1”).ClearContents

or simply use:
Range(“A1”).Value = “”

Note: use the Clear method to clear the content and format of a range. Use the ClearFormats method to clear the format only.

Count

With the Count property, you can count the number of cells, rows and columns of a range.

Count Property

Note: border for illustration only.

Code:
Dim example As Range
Set example = Range(“A1:C4”)

MsgBox example.Count

Count Cells

Code:
Dim example As Range
Set example = Range(“A1:C4”)

MsgBox example.Rows.Count

Count Rows

Note: in a similar way, you can count the number of columns of a range.

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA , Loop Tutorial

Excel VBA Macro Tutorial , How to use Loop


Single Loop    |   Double Loop    |   Triple Loop    |   Do While Loop

Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.
Single Loop

You can use a single loop to loop through a one-dimensional range of cells.

Place a command button on your worksheet and add the following code lines:

Dim i As Integer

For i = 1 To 6
    Cells(i, 1).Value = 100
Next i

Single Loop in Excel VBA

Explanation: The code lines between For and Next will be executed six times. For i = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For statement. For i = 2, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc.

Note: it is good practice to always indent (tab) the code between the words For and Next. This makes your code easier to read.
Double Loop

You can use a double loop to loop through a two-dimensional range of cells.

Place a command button on your worksheet and add the following code lines:
Dim i As Integer, j As Integer

For i = 1 To 6
    For j = 1 To 2
        Cells(i, j).Value = 100
    Next j
Next i

Double Loop in Excel VBA

Explanation: For i = 1 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next j, it increases j with 1 and jumps back to the For j statement. For i = 1 and j = 2, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 2. Next, Excel VBA ignores Next j because j only runs from 1 to 2. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For i statement. For i = 2 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc.
Triple Loop

You can use a triple loop to loop through two-dimensional ranges on multiple Excel worksheets.

Place a command button on your worksheet and add the following code lines:
Dim c As Integer, i As Integer, j As Integer

For c = 1 To 3
    For i = 1 To 6
        For j = 1 To 2
            Worksheets(c).Cells(i, j).Value = 100
        Next j
    Next i
Next c

Explanation: The only change made compared to the code for the double loop is that we have added one more loop and added Worksheets(c). in front of Cells to get the two-dimensional range on the first sheet for c = 1, the second sheet for c = 2 and the third sheet for c = 3. Download the Excel file to see this result.
Advertisement
Do While Loop

Besides the For Next loop, there are other loops in Excel VBA. For example, the Do While Loop. Code placed between Do While and Loop will be repeated as long as the part after Do While is true.

1. Place a command button on your worksheet and add the following code lines:
Dim i As Integer
i = 1

Do While i < 6
    Cells(i, 1).Value = 20
    i = i + 1
Loop

Do While Loop

Explanation: as long as i is lower than 6, Excel VBA enters the value 20 into the cell at the intersection of row i and column 1 and increments i by 1. In Excel VBA (and in other programming languages), the symbol ‘=’ means becomes. It does not mean equal. So i = i + 1 means i becomes i + 1. In other words: take the present value of i and add 1 to it. For example, if i = 1, i becomes 1 + 1 = 2. As a result, the value 20 will be placed into column A five times (not six because Excel VBA stops when i equals 6).

2. Enter some numbers in column A.

3. Place a command button on your worksheet and add the following code lines:
Dim i As Integer
i = 1

Do While Cells(i, 1).Value <> “”
    Cells(i, 2).Value = Cells(i, 1).Value + 10
    i = i + 1
Loop

Advanced Do While Loop

Explanation: as long as Cells(i, 1).Value is not empty (<> means not equal to), Excel VBA enters the value into the cell at the intersection of row i and column 2, that is 10 higher than the value in the cell at the intersection of row i and column 1. Excel VBA stops when i equals 7 because Cells(7, 1).Value is empty. This is a great way to loop through any number of rows on a worksheet.

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA Macro, Tutorial Create Function and Sub

Create Function and Sub

The difference between a function and a sub in Excel VBA is that a function can return a value while a sub cannot. Functions and subs become very useful as program size increases.
Function

If you want Excel VBA to perform a task that returns a result, you can use a function. Place a function into a module (In the Visual Basic Editor, click Insert, Module). For example, the function with name Area.

Function Area(x As Double, y As Double) As Double
Area = x * y

End Function

Explanation: This function has two arguments (of type Double) and a return type (the part after As also of type Double). You can use the name of the function (Area) in your code to indicate which result you want to return (here x * y).

You can now refer to this function (in other words call the function) from somewhere else in your code by simply using the name of the function and giving a value for each argument.

Place a command button on your worksheet and add the following code lines:

Dim z As Double

z = Area(3, 5) + 2

MsgBox z

Explanation: The function returns a value so you have to ‘catch’ this value in your code. You can use another variable (z) for this. Next, you can add another value to this variable (if you want). Finally, display the value using a MsgBox.

Result when you click the command button on the sheet:

Sub

If you want Excel VBA to perform some actions, you can use a sub. Place a sub into a module (In the Visual Basic Editor, click Insert, Module). For example, the sub with name Area.

Sub Area(x As Double, y As Double)

MsgBox x * y

End Sub

Explanation: This sub has two arguments (of type Double). It does not have a return type! You can refer to this sub (call the sub) from somewhere else in your code by simply using the name of the sub and giving a value for each argument.

Place a command button on your worksheet and add the following code line:

Area 3, 5

Result when you click the command button on the sheet:

Can you see the difference between the function and the sub? The function returned the value 15. We added the value 2 to this result and displayed the final result. When we called the sub we had no more control over the result (15) because a sub cannot return a value!

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Excel VBA Macro, Using Date and Time, DateAdd

Tutorial Excel VBA belajar menggunakan Date and Time

Melalui module di VBE ketik script berikut

Code:

Deklarasi variable Date dengan Dim
Dim exampleDate As Date 



‘Variable diisi dengan function untuk mengambil nilai dari data tgl
exampleDate = DateValue(“Jun 19, 2010”)

‘Test dengan melihat hasilnya di Msgbox
MsgBox Year(exampleDate)

Note: Gunakan Month dan Day untuk ambil nilai bulan dan hrii

DateAdd

Digunakan untuk mendapatkan perpanjangan hari dari tanggal yang ditentukan

Code:
‘deklarasi variable
Dim firstDate As Date, secondDate As Date‘Isi variable
firstDate = DateValue(“Jun 19, 2010”)
secondDate = DateAdd(“d”, 3, firstDate)
‘test dengan msgbox
MsgBox secondDate

Note: Ganti d” ke  “m” untuk perpanjangan bulan dari tanggal ditentukan

current date and time, gunakan Now function.

Code:
MsgBox Now

Current Date Time
Hour, Minute, Second

Mendapatkan Jam(Hour), Gunakan Hour function.

Code:
MsgBox Hour(Now)

Time value untuk convert string ke Jam

Code:

MsgBox TimeValue(“9:20:01 am”)

TimeValue

Dim y As Double
y = TimeValue(“09:20:01”)
MsgBox y

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Freelance IT Trainer, Pengajar Komputer, Inhouse Trainer, Management Trainer , Hery Purnama

Hery Purnama 081.223344.506 , Trainer Jakarta, Bandung, Yogya, Indonesia, Freelance IT Trainer, Pengajar Komputer Lepas , Instruktur komputer, Inhouse Training Certified IT Trainer.

Cari Pengajar , Trainer IT / Komputer untuk inhouse training di perusahaan Anda , Hubungi Hery Purnama 081223344506 , Berpengalaman lebih dari 15 tahun dan Certified

Hery Trainer/ Pengajar Excel VBA , Android Phonegap, Google Map API, Google Sketchup3D , MS Project, ExtJS, Oracle, SQL Server, MySQL, MS. Access VBA, RDBMS Concept, PHP , Ajax JQUERY, Yii Framework, Code Igniter, PHP SMS Gateway, Photoshop, CorelDraw, UML, Facebook API Developer dll.. Berpengalaman 15 Tahun sebagai Trainer bersertifikasi CIW, OCA, ITILF, MCP, MOS

Topics

  • Google Application Integration Toolbox  for web developer
    Building Integrated Map Website with PHP MySQL and Google Map API V.3
    Web Integration with Facebook API developer toolbox
    Web Development with CMS WordPress
    Web Development with PHP Yii Framework
    Web Development with PHP CodeIgniter
    Project Management Methodologies  With Microsoft Project 2010
    SEO and Internet Marketing
    RDBMS – Database Concept
    ITIL V.3 Foundation
    Building Database Application with Microsoft Access 2010
    Microsoft Access 2010 VBA Macro programming
    MYSQL Server DBA Fundamental
    Object Manipulation with Adobe Photoshop CS
    CorelDraw X6 for Marketing and Promotion
    Google Sketchup 3D for Building Interior
    Building Flash Animation with SWISHMAX
    Building PHP MySQL – AJAX Web Application with Adobe Dreamweaver CS
    Building  ASP.Net Application with IronSpeed Designer
    Building PHP MySQL Web Application with CodeCharge Studio
    Mobile Application Development for Android with Jquery Mobile, MySQL & Phonegap
    HTML 5 for Mobile Application Development
    Microsoft Office 2010 (WORD, EXCEL, POWERPOINT)
    Microsoft Excel Visual Basic for Application (VBA-Macro)
    Networking Essential
    Installing, Configuring and Administering Windows Server 2008
    Installing, Configuring and Administering UBUNTU
    Installing and Configuring Exchange Server 2008
    CISCO Router
    Application Programming with VB.NET
    Web and Application programming with ASP.NET
    Web and Application programming with PHP – MYSQL
    Building Rich Internet Application  with PHP and Sencha ExtJs
    Building web application with PHP MySQL & AJAX Jquery
    Building Event Scheduler System with PHP AJAX and SMS Gateway
    Computer for Secretary and Admin Staff (Networking  Essential, MS. Outlook, Document Security and sharing, MS. Office Integration, File Archive and data backup – Recovery, etc.)
    Kerio Winroute Firewall
    SQL Server 2008 Database Design & DBA Fundamental

My Clients:

  • PT. Sampoerna Tbk – Bandung
  • PT. Asuransi AXA Indonesia – Jakarta
  • Icon Plus (Icon PLN – jakarta)
  • Bank Ekonomi – Jakarta
  • PT PLN (Persero) PUSHARLIS UWP III
  • PT. Karenindo Citra Utama – Jakarta
  • ITENAS – Bandung
  • Universitas Bina Nusantara (BINUS – Jakarta)
  • Bank Jabar Banten (BJB)
  • AMDOCS – Jakarta
  • PT. Elnusa, Tbk.
  • PT. Sarana Multigriya Finansial / SMF (Persero)
  • PT. Pertamina Geothermal Energy (Thamrin – Jakarta)
  • PT. Indonesia Asahan Alumunium (INALUM- Medan)
  • UIN SUSKA – Riau
  • Rumah Sakit AWALBROS Bekasi
  • Universitas Andalas
  • Politeknik ACEH
  • Pusinfowas BPKP – Jakarta
  • Asuransi Takaful – Jakarta
  • PT. Freeport , Tbk – Tembagapura Papua
  • PT. Gerbang Sinergi Prima – Bandung
  • PT PLN (PERSERO) UIP TRANSMISI ISJ – jakarta
  • PT. CG Power System Indonesia – Bogor
  • PT. Pertamina (Persero) – Jakarta
  • Bank CIMB Niaga –  Jakarta
  • Ikatan Mahasiswa Teknik Metalurgi ITB – Bandung
  • PT. Pupuk Kijang – Cikampek
  • PT. Pertamina (Persero – Jakarta)
  • Bakrie Telecom Jakarta
  • SMKN 2 Garut
  • PT BES Kelapa Gading Jakarta
  • PT. Telkomsel – Jakarta
  • PT. Hutama Karya Persero (Bandung)
  • PT. Seascape Survey Indonesia
  • PT. Pupuk Kaltim
  • Dana Pensiun Pupuk Kaltim
  • PT. Sarinah (Persero)
  • PT. Weda Bay Nickel (Halmahera)
  • Taq-Taq Operating Company Ltd (Kurdhistan – Irak)
  • EMP MALACCA STRAIT SA – Jakarta
  • Bank BTPN Jakarta
  • PT. Sarana Multi Infrastruktur (Persero)
  • PT. Medco Energi – Jakarta
  • PT. ANTAM, Tbk.
  • Unversitas Islam Indonesia (Teknik Informatika)
  • Institut Teknologi Telkom Bandung
  • Politeknik Negeri Malang
  • PT. KPEI
  • DPPKAD Kabupaten Gresik
  • PT PJB UPHB
  • PT. Krakatau Steel


    Building Integrated Map Website with PHP MySQL and Google Map API V.3
    (Inhouse ICON PLN Jakarta)
    icon PLN inhouse sisindotek
    Inhouse Training ini membahas tentang bagaimana membuat peta digital interaktif dan dinamis dengan informasi yang koordinat / LatLong yang tersimpan di database lalu ditampilkan secara dinamis menggunakan Google MAP API V.3 , beberapa metode yang dipelajari seperti Single Marker, Multiple Marker, Polyline, Geofence, Geocode, Direction, Single Marker, Multiple marker Custom Marker , Polyline dan lainnya.
     
    Microsoft Excel Advance for Business, Accounting & Management (Bank BJB – 4 Angkatan)
    Bank BJB inhouse sisindotek
    Kegiatan inhouse training dengan Bank BJB sebanyak 4 angkatan dengan total peserta 26 orang membahas pemanfaat excel dengan tingkatan yang lebih tinggi berupa pemanfaatn formula, function baik yang sudah umum ataupun yang tidak umum untuk membuat kemudahaan dalam pengelolaan data buat kebutuhan bisnis, Akuntansi ataupun manajemen.
     
    Building Event Scheduller Application with PHP and SMS Gateway (PLN)
    Training ini kembali diikuti oleh klien exclusive group dari PT PLN (PERSERO) UIP TRANSMISI ISJ, training ini membahas cara
    membuat aplikasi Event Scheduller dengan PHP, MySQL dan SMS gateway sebagai notification systemnya
     
    Mobile Application development for Android using Jquery Mobile and Phonegap (Bakrie Telecom)
    Inhouse Training membahas pemanfaat framework JQM dan phonegap dipadukan dengan PHP Ajax untuk membuat aplikasi berbasis Android dengan mudah
     
    Project Management Methodologies with MS Project (Inhouse Training INALUM Medan)
    Inhouse Training Project Management with MS. Project bersama staff dari PT. Indonesia Asahan Alumunium (INALUM) , Training membahas konsep Project Management dan implementasinya ke dalam software MS. Project 2010, termasuk dalam pembahasan adalah Project Management Lifecycle, Cost, Budget dan metodologi seperti PERT, CPM, PDM dan juga Earned Value Concept (SV, CV, CPI, SPI, BCWS, BCWP, ACWP dll.
     
    Excel VBA Macro (Inhouse training PGE Indonesia)
    Training Excel VBA Macro Programming bersama staff dari PT. Pertamina Geothermal Energy – Jakarta, Training ini membahas pemograman
    di Excel untuk melakukan otomatisasi dan pengembangan kinerja excel untuk memenuhi kebutuhan yang lebih kompleks
     
    ITIL v3 Foundation (Exclusive Group)
    Training ITIL v.3 Foundation yang diselenggarakan di Hotel Bintang 4 (DeJava Hotel , PVJ Bandung) diikuti oleh 3 peserta dari staff PUSINFOWAS BPKP jakarta
    Training ini bertujuan memahami konsep dari pengelolaan IT berbasis layanan (IT Service Management) berdasarkan framework IT Infrastructure Library (ITIL) versi 3. Beserta tambahan skenario studi kasusnya dan implementasi menggunakan ITSM software simulation serta simulasi persiapan ujian ITIL
     
    Excel VBA Macro (Exclusive Group)
    Training Microsoft Excel Visual Basic for Application (VBA – Macro) bersama 7 peserta dari PT. Freeport , Tbk (Exclusive Group) yang terbagi atas 3 gelombang / Periode
    Training ini membahas Pengenalan dan pemanfaatan Macro dan pemograman VBA untuk menyempurnakan dan memaksimalkan penggunaan Microsoft Excel lebih diatas penggunaan standard pada umumnya. pengenalan objek Excel dan VBA memanipulasi objek, penggunaan control , active x , array, looping , membuat function merupakan beberapa materi yang diberikan dalam pelatihan ini. Dikarenakan paket exclusive group maka training ini juga khusus membahas kasus khusus PT Freeport untuk diterapkan di bagian planner PT Freeport sesuai kebutuhan pekerjaan.
     
    Building Integrated Map Website with PHP MySQL & Google MAP API V.3
    Training Building Integrated Map Website with PHP and Google Map API V.3 diikuti oleh peserta Exclusive Group dari PT PLN (PERSERO) UIP TRANSMISI ISJ
    Training ini membahas tentang bagaimana membuat peta digital interaktif dan dinamis dengan informasi yang koordinat / LatLong yang tersimpan di database lalu ditampilkan secara dinamis menggunakan Google MAP API V.3 , beberapa metode yang dipelajari seperti Single Marker, Multiple Marker, Polyline, Geofence, Geocode, Direction, Custom Marker dan lainnya.
     
    Inhouse Training Microsoft Excel VBA Macro
    Inhouse Training Microsoft Excel Visual Basic for Application (VBA – Macro) bersama 15 peserta dari Ikatan Mahasiswa Teknik Metalurgi ITB Bandung
    Training ini membahas Pengenalan dan pemanfaatan Macro dan pemograman VBA untuk menyempurnakan dan memaksimalkan penggunaan Microsoft Excel lebih diatas penggunaan standard pada umumnya. pengenalan objek Excel dan VBA memanipulasi objek, penggunaan control , active x , array, looping , membuat function merupakan beberapa materi yang diberikan dalam pelatihan ini.
     
    Exclusive Group Training Project Management Methodologies with MS. Project 2010 PT.ANTAM

    Pelatihan Project Management Methodologies with MS. Project 2010 bersama 6 peserta Exclusive Group dari PT. ANTAM, Tbk. (Aneka Tambang) Sulawesi Tenggara
    Training ini membahas konsep Project Management beserta metodologi yang diimplementasikan menggunakan MS. Project , pembahasan teori berupa Konsep CPM, PDM, Earned Value (BCWS, SV, CV, SPI dll) Juga dibahas untuk memberi bekal mendalam perihal Project Management.
    disertai pembahasan studi kasus
    Kurva S
Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT

Info Trainer, Pengajar Lepas SMS Gateway

Hery Purnama 081.223344.506 , Jakarta, Bandung, Yogya, Indonesia, Freelance IT Trainer, Pengajar Komputer Lepas , Instruktur komputer.

Cari Pengajar , Trainer IT / Komputer untuk inhouse training di perusahaan Anda , Hubungi Hery Purnama 081223344506 , Berpengalaman lebih dari 15 tahun dan Certified

Hery Trainer/ Pengajar Excel VBA , Android Phonegap, Google Map API, Google Sketchup3D , MS Project, ExtJS, Oracle, SQL Server, MySQL, MS. Access VBA, RDBMS Concept, PHP , Ajax JQUERY, Yii Framework, Code Igniter, PHP SMS Gateway, Photoshop, CorelDraw, UML, Facebook API Developer dll.. Berpengalaman 15 Tahun sebagai Trainer bersertifikasi CIW, OCA, ITILF, MCP, MOS

Hery Purnama – 081.223344.506 – inhousetrainer@yahoo.com.
Freelance IT Trainer, Trainer Excel VBA Macro, Trainer Sencha ExtJS, Trainer Google Map API, Trainer SPSS, Trainer MS Project, Trainer Primavera, Trainer Google Sktechup 3D, Trainer Android Phonegap, Trainer Oracle, Trainer SQL Server, Trainer MySQL, Trainer PHP Jquery Mobile , Trainer Java, Trainer HTML5 Javascript, Trainer SEO Internet Marketing, Trainer Project Management, Trainer SMS Gateway, Trainer CMS WordPress, ITIL, COBIT