coretan tentang autocad dan excel

November 18, 2012

[XLS-SVY-006]:Menghitung Luas Cross Section dengan Visual Basic Application Excel (Macro)

Filed under: Excel, Tukang Ukur — Tags: , — cadex @ 23:36
Referensi : https://cadex.wordpress.com/2010/12/05/xls-svy-004membuat-grafik-dan-menghitung-luas-cross-section-dengan-excel-bagian-2/
Platform : Excel
File :

Pada post [XLS-SVY-004]:Membuat Grafik dan Menghitung Luas Cross Section dengan Excel Bagian #2, telah diuraikan tahapan menghitung luas cross section dengan menggunakan rumus / fungsi excel (tanpa macro). Pada hitungan metode ini, untuk mengurangii panjangnya rumus diperlukan beberapa cell bantu. Penggunakaan cell bantu tentunya akan memperbesar ukuran file excel, apalagi kalo data yang akan diolah terdiri dari beberapa station.

Dengan visual basic application, cell bantu tersebut dapat dihilangkan, sehingga didapat fungsi baru (user defined function/UDF) yang lebih simple.

Download file Download program & contoh cross. Setelah didownload dan diextract filenya, buka file xs.xla, kemudian file contoh cross sectionnya. Click [Enable Macro] saat membuka file xs.xla

Contoh cross section :

image

Dalam file xs.xla ada dua UDF yaitu:

1. xCatchPoint()

2. xCutFill()

1. Format Fungsi xCatchPoint():

=CatchPoint(PolylineExisting ,OffsetTemplateKiri ,OffsetTemplateKanan,ElevasiTemplateKiri ,ElevasiTemplateKanan ,SlopeCatchPointKiri ,SlopeCatchPointKanan)

Fungsi ini akan menghasilkan 4 (empat) angka yaitu Offset, Elevasi catch point Kiri dan Offset Elevasi catch point kanan.

[B11]=Index(xCatchPoint($Q$7:$R$27,$B$7,$C$7,$B$9,$C$9,$B$10,$C$10),1)
[B12]=Index(xCatchPoint($Q$7:$R$27,$B$7,$C$7,$B$9,$C$9,$B$10,$C$10),2)
[C11]=Index(xCatchPoint($Q$7:$R$27,$B$7,$C$7,$B$9,$C$9,$B$10,$C$10),3)
[C12]=Index(xCatchPoint($Q$7:$R$27,$B$7,$C$7,$B$9,$C$9,$B$10,$C$10),4)

2. Format Fungsi xCutFillX():

=xCutFill(PolylineExisting, PolylineDesign, TypeRtn)

TypeRtn=0, untuk menampilkan hasil hitungan luas Cut dan Fill. Gunakan index=1, untuk mengambil luas Cut, dan index=2 untuk mengambil lus Fill.

[G8]=INDEX(xCutFill($Q$7:$R$27,$S$7:$T$11,0),1)
[H8]=INDEX(xCutFill($Q$7:$R$27,$S$7:$T$11,0),2)

Coba rubah-rubah parameter design di seperti elevasi design, slope, grade, offset template dll, maka otomatis luas cross section dan grafik akan terupdate

Dalam contoh data cross section ada dua contoh cross section sehingga masing-masing dapat dihitung luas cut dan luas fillnya. Ada tambahan satu sheet [Resume] untuk menghitung volume cut dan fill seperti gambar di bawah:

image

Catatan Penting:

1. Program ini memerlukan fungsi UDF yang disimpan dalam file xs.xla sehinga apabila file cross section dicopy ke tempat lain yang tidak mempunyai file xs.xla, maka graphic dan hitungan luas tidak bisa dihitung” Tampilan jika file xs.xla belum dibuka adalah:

image

2. Copy >> Paste >> Value rumus yang menggunakan fungsi xCatchPoint() dan xCutFill() sebelum didistribusikan.

3. Jika diinginkan setiap kali menjalan program excel file xs.xla otomatis terbuka lakukan pengatuan setting Add-In. Kalo pakai Excel 2007 lakukan pengaturan sebagai berikut:

  • Click tombol image , kemudian pilih [Excel Option] kemudian [Add-Ins]
  • Dalam pilihan [Manage], pilih [Excel Add-Ins] kemudian click tombol [Go]
  • Pada pilihan [Add-Ins], click tombol [Browse]
  • Pilih lokasi file xs.xla, sehingga tampilan Add-Ins yang aktif menjadi

image

 

Note: 8 Feb 2014

the xla program (xs.xla) has been revised since the previous program had “calculation errors” on the right catch point.

thanks for Khalid Maqbool for finding these errors.

 

‘============== selamat mencoba =========================

9 Comments »

  1. Sip. Simpan dulu, pasti berguna.
    Terus berkarya …

    Comment by Adi Isdiarto — November 19, 2012 @ 20:35

  2. Terima kasih ya, akan dicuba nanti. by the way, saya juga surveyors, daripada Malaysia. ini link ke blog saya, juga berkitarkan environment survey di Malaysia, http://jurukurtanah.blogspot.com/ . Salam perkenalan.

    Comment by Arif — January 16, 2013 @ 11:34

    • salam kenal, semoga bermanfaat. kalo saya survyor online, karena sekarang banyak bekerja di project management

      Comment by cadex — January 16, 2013 @ 13:45

  3. oia pak klo boleh thu pasward bt membuka file xs.xla apa ???
    terima kasih

    Comment by eva — August 26, 2013 @ 14:30

  4. Salam. Dear I downloaded your file and found that if all the elevations of natural ground are same e.g left side end offset is 98m up to 0 is also 98m and the right side is also 98m (in your file Q7 to R27). And if the elevation of designed cross section on each end is 100 e.g in your file (B9 & C9). So the catch point in B11 & C11 must be same. Means if all data is same figures then right and left catch points must be same in value. I think you should re-calculate your file. Please inform me for your design structure that what is your method application for designing x-sections in your country. If as your file is according to statement of work, design and specifications of the project in your country then its OK, otherwise give attention to my question and inform me for the formulas used in this file and re correction of the file.
    Regards

    Comment by Khalid Maqbool — February 7, 2014 @ 20:20

    • wa’alaikum salam,
      it’s very glad having your comments and thanks for finding the error. Please re-download to get the revised calculation.
      thanks
      -zainul-

      Comment by cadex — February 8, 2014 @ 09:52

  5. Hi there,
    Very interested in having a look at this spreadsheet as it may be exactly what I am looking for to help figure out a project in Canada! Could please you provide a brief list of instructions in English. This may help https://wordpress.org/plugins/best-seo-itranslator-for-wordpress/

    Thank you very much

    Comment by Clint — August 1, 2014 @ 02:27

  6. terima kasih ulasanya,mohon file rumusnya pak,email saya kufindatrs51@ymail.com

    Comment by kufinda — November 10, 2015 @ 23:46

  7. Aslam o Alikum dear sir,

    basically i find xls to cad profile & cross section plotting sheet, i see in other office the plot profile & cross section in cad via xls sheets. is it possible to shared me if you have. thanks and regards.

    Comment by nomee — November 13, 2015 @ 18:38


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: