Microsoft Office Excel ® Kod Kılavuzu

Microsoft Office Excel® Code Guide

20 Mayıs 2011 Cuma

Degree Of Operating Leverage Affects Profitability Analysis Of The Paid-Up Capital in Excel





'Module1


'A) Windows XP® Office 2003® Normal Referance List

'Description: Visual Basic For Applications, FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

'Description: Microsoft Excel 11.0 Object Library, FullPath: C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE'Description: OLE Automation, FullPath: C:\WINDOWS\system32\stdole2.tlb
'Description: Microsoft Office 11.0 Object Library, FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL

'B) LEVERAGE AFFECTS PROFİTABİLİTY ANALYSİS OF THE PAİD-UP CAPİTAL

'Dr. Öztin AKGÜÇ; 1998 Finansal Yönetim ISBN:9757429090

Option Explicit
Sub OLFLCLDüzenle()

On Error Resume Next
Sheets("OL").Select
If VBA.Err.Number = 0 Then

Call DüzenlemeYap("OL", "DEGREE OF OPERATİNG LEVERAGE AFFECTS PROFİTABİLİTY ANALYSİS OF THE PAİD-UP CAPİTAL.", "=+R[25]C[-4]", "=+R[25]C[-6]", "=+R[13]C[-5]", "=+R[23]C[-5]", "=+R[10]C[-5]", "=+R[13]C[-7]", "=+R[23]C[-7]", "=+R[10]C[-7]", "=R[18]C[-7]", "=+R[18]C[-8]", 3850000, 1875000, 635000, 519000, 912500, 835000, 278500, 34800, 65000, 2400000, 0, 0, 0.1, 0, 0)

Else

VBA.Err.Number = 0
ThisWorkbook.Worksheets.Add Before:=Sheets(1)
ActiveSheet.Name = "OL"
Call DüzenlemeYap("OL", "DEGREE OF OPERATİNG LEVERAGE AFFECTS PROFİTABİLİTY ANALYSİS OF THE PAİD-UP CAPİTAL.", "=+R[25]C[-4]", "=+R[25]C[-6]", "=+R[13]C[-5]", "=+R[23]C[-5]", "=+R[10]C[-5]", "=+R[13]C[-7]", "=+R[23]C[-7]", "=+R[10]C[-7]", "=R[18]C[-7]", "=+R[18]C[-8]", 3850000, 1875000, 635000, 519000, 912500, 835000, 278500, 34800, 65000, 2400000, 0, 0, 0.1, 0, 0)

End If
Sheets("FL").Select
If VBA.Err.Number = 0 Then

Call DüzenlemeYap("FL", "DEGREE OF FINANCIAL LEVERAGE AFFECTS PROFITABILITY ANALYSIS OF THE PAID-UP CAPITAL.", "=+R[25]C[-4]", "=+R[25]C[-6]", "=+R[17]C[-5]", "=+R[13]C[-5]", "=+R[13]C[-5]", "=+R[17]C[-7]", "=+R[13]C[-7]", "=+R[13]C[-7]", "=R[19]C[-7]", "=+R[19]C[-8]", 3850000, 1875000, 0, 519000, 912500, 0, 0, 0, 65000, 2400000, 789000, 255000, 0.1, 0.09, 0.12)

Else

VBA.Err.Number = 0
ThisWorkbook.Worksheets.Add Before:=Sheets(1)
ActiveSheet.Name = "FL"
Call DüzenlemeYap("FL", "DEGREE OF FINANCIAL LEVERAGE AFFECTS PROFITABILITY ANALYSIS OF THE PAID-UP CAPITAL.", "=+R[25]C[-4]", "=+R[25]C[-6]", "=+R[17]C[-5]", "=+R[13]C[-5]", "=+R[13]C[-5]", "=+R[17]C[-7]", "=+R[13]C[-7]", "=+R[13]C[-7]", "=R[19]C[-7]", "=+R[19]C[-8]", 3850000, 1875000, 0, 519000, 912500, 0, 0, 0, 65000, 2400000, 789000, 255000, 0.1, 0.09, 0.12)

End If
Sheets("CL").Select
If VBA.Err.Number = 0 Then

Call DüzenlemeYap("CL", "AFFECT THE DEGREE OF LEVERAGE COMBİNED PAİD-İN CAPİTAL PROFİTABİLİTY ANALYSİS.", "=+R[13]C[-4]", "=+R[13]C[-6]", "=+R[17]C[-5]", "=+R[13]C[-5]", "=+R[13]C[-5]", "=+R[17]C[-7]", "=+R[13]C[-7]", "=+R[13]C[-7]", "=R[20]C[-7]", "=+R[20]C[-8]", 3850000, 1875000, 635000, 519000, 912500, 835000, 278500, 34800, 65000, 2400000, 789000, 255000, 0.1, 0.09, 0.12)

Else

VBA.Err.Number = 0
ThisWorkbook.Worksheets.Add Before:=Sheets(1)
ActiveSheet.Name = "CL"
Call DüzenlemeYap("CL", "AFFECT THE DEGREE OF LEVERAGE COMBİNED PAİD-İN CAPİTAL PROFİTABİLİTY ANALYSİS.", "=+R[13]C[-4]", "=+R[13]C[-6]", "=+R[17]C[-5]", "=+R[13]C[-5]", "=+R[13]C[-5]", "=+R[17]C[-7]", "=+R[13]C[-7]", "=+R[13]C[-7]", "=R[20]C[-7]", "=+R[20]C[-8]", 3850000, 1875000, 635000, 519000, 912500, 835000, 278500, 34800, 65000, 2400000, 789000, 255000, 0.1, 0.09, 0.12)

End If

End Sub
Private Function DüzenlemeYap(Sayfa, Etiket, Kay1, Kay2, Use1, Use2, Use3, Use4, Use5, Use6, Kon1, Kon2, a, b, c, e, f, j, k, l, s, ac, ad, ae, ag, ah, ai)

On Error Resume Next
Sheets(Sayfa).Select
ActiveSheet.Unprotect
Cells.Select
With Selection.Font

.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone

End With
[A1].Select
Range("B4").FormulaR1C1 = "Description (İşlem Tanımı)"
Range("B6").FormulaR1C1 = "a"
Range("B7").FormulaR1C1 = "b"
Range("B8").FormulaR1C1 = "c"
Range("B9").FormulaR1C1 = "d=b+c"
Range("B10").FormulaR1C1 = "e"
Range("B11").FormulaR1C1 = "f"
Range("B12").FormulaR1C1 = "g=e-f"
Range("B13").FormulaR1C1 = "h=d+g"
Range("B14").FormulaR1C1 = "i=a-h"
Range("B15").FormulaR1C1 = "j"
Range("B16").FormulaR1C1 = "k"
Range("B17").FormulaR1C1 = "l"
Range("B18").FormulaR1C1 = "m=j+k+l"
Range("B19").FormulaR1C1 = "n=i-m"
Range("B20").FormulaR1C1 = "o"
Range("B21").FormulaR1C1 = "p"
Range("B22").FormulaR1C1 = "q=o+p"
Range("B23").FormulaR1C1 = "r=n-q"
Range("B24").FormulaR1C1 = "s"
Range("B25").FormulaR1C1 = "t=r+s"
Range("B26").FormulaR1C1 = "u=t*%40"
Range("B27").FormulaR1C1 = "v=t-u"
Range("B28").FormulaR1C1 = "w=r/af"
Range("B29").FormulaR1C1 = "x1=b-(f*b/d)"
Range("B30").FormulaR1C1 = "x2=b-(f*b/d)"
Range("B31").FormulaR1C1 = "y=a-x1"
Range("B32").FormulaR1C1 = "z=y/n"
Range("B33").FormulaR1C1 = "aa=n/r"
Range("B34").FormulaR1C1 = "ab=z*aa"
Range("B35").FormulaR1C1 = "ac"
Range("B36").FormulaR1C1 = "ad"
Range("B37").FormulaR1C1 = "ae"
Range("B38").FormulaR1C1 = "af=ac-(ad+ae)"
Range("B39").FormulaR1C1 = "ag"
Range("B40").FormulaR1C1 = "ah"
Range("B41").FormulaR1C1 = "ai"
Range("B42").FormulaR1C1 = "aj"
Range("C6").FormulaR1C1 = "Net Sales"
Range("C7").FormulaR1C1 = "Variable Production Cost"
Range("C8").FormulaR1C1 = "Semi-Fixed and variable costs of production"
Range("C9").FormulaR1C1 = "Total Production Costs"
Range("C10").FormulaR1C1 = "Products per semester Stocks"
Range("C11").FormulaR1C1 = "Final Product Stocks"
Range("C12").FormulaR1C1 = "Change in product inventory"
Range("C13").FormulaR1C1 = "Cost of Sales"
Range("C14").FormulaR1C1 = "Gross Profit / Loss"
Range("C15").FormulaR1C1 = "Marketing Sales and Distribution Expenses"
Range("C16").FormulaR1C1 = "General and Administrative Expenses"
Range("C17").FormulaR1C1 = "Research and development (R & D) Expenses"
Range("C18").FormulaR1C1 = "Collect Operating Expenses"
Range("C19").FormulaR1C1 = "Operating Profit / Loss"
Range("C20").FormulaR1C1 = "Short Term Loan Interest and Expenses"
Range("C21").FormulaR1C1 = "Medium-and Long-Term Credit Interest Expenses"
Range("C22").FormulaR1C1 = "Total Financing Costs"
Range("C23").FormulaR1C1 = "Operating Profit / Loss"
Range("C24").FormulaR1C1 = "Non-Profit or Loss for Business"
Range("C25").FormulaR1C1 = "Profit Before Tax and Legal Liabilities or Loss"
Range("C26").FormulaR1C1 = "Institutions Etc. Gain Tax. Legal Provisions"
Range("C27").FormulaR1C1 = "Profit or Loss After Tax and Legal Liabilities"
Range("C28").FormulaR1C1 = "Return on Paid-In Capital"
Range("C29").FormulaR1C1 = "Total Variable Manufacturing Costs"
Range("C30").FormulaR1C1 = "Total Semi-Fixed Manufacturing Costs"
Range("C31").FormulaR1C1 = "Total Net Contribution"
Range("C32").FormulaR1C1 = "Degree of Operating Leverage"
Range("C33").FormulaR1C1 = "Degree of Financial Leverage"
Range("C34").FormulaR1C1 = "Degree of Combined Leverage"
Range("C35").FormulaR1C1 = "Total Resources"
Range("C36").FormulaR1C1 = "Short Term Loans"
Range("C37").FormulaR1C1 = "Medium Term Loans"
Range("C38").FormulaR1C1 = "Paid-in Capital"
Range("C39").FormulaR1C1 = "Capacity Change Rate"
Range("C40").FormulaR1C1 = "Short Term Loan Interest Rates and Charges"
Range("C41").FormulaR1C1 = "Medium Term Loan Interest Rates and Charges"
Range("C42").FormulaR1C1 = "Control"
Range("D6").FormulaR1C1 = "Net Satış Hasılatı"
Range("D7").FormulaR1C1 = "Deişken Üretim Maliyetleri"
Range("D8").FormulaR1C1 = "Sabit ve Yarı Değişken Üretim Maliyetleri"
Range("D9").FormulaR1C1 = "Toplam Üretim Maliyetleri"
Range("D10").FormulaR1C1 = "Dönem Başı Mamul Stokları"
Range("D11").FormulaR1C1 = "Dönem Sonu Mamul Stokları"
Range("D12").FormulaR1C1 = "Mamul Stoklarındaki Değişim"
Range("D13").FormulaR1C1 = "Satışların Maliyeti"
Range("D14").FormulaR1C1 = "Brüt Satış Karı / Zararı"
Range("D15").FormulaR1C1 = "Pazarlama Satış ve Dağıtım Giderleri"
Range("D16").FormulaR1C1 = "Genel Yönetim Giderleri"
Range("D17").FormulaR1C1 = "Araştırma ve Geliştirme (AR-GE) Giderleri"
Range("D18").FormulaR1C1 = "Toplama Faaliyet Giderleri"
Range("D19").FormulaR1C1 = "Faaliyet Karı / Zararı"
Range("D20").FormulaR1C1 = "Kısa Vadeli Kredi Faiz ve Giderleri"
Range("D21").FormulaR1C1 = "Orta-Uzun Vadeli Kredi Faiz ve Giderleri"
Range("D22").FormulaR1C1 = "Toplam Finansman Giderleri"
Range("D23").FormulaR1C1 = "İşletme Karı / Zararı"
Range("D24").FormulaR1C1 = "İşletme Dışı Kar veya Zarar"
Range("D25").FormulaR1C1 = "Vergi ve Yasal Yükümlülükler Öncesi Kar veya Zarar"
Range("D26").FormulaR1C1 = "Kurum Kazancı Vergi vb. Yasal Yükümlülük Karşılıkları"
Range("D27").FormulaR1C1 = "Vergi ve Yasal Yükümlülükler Sonrası Kar veya Zarar"
Range("D28").FormulaR1C1 = "Ödenmiş Sermaye Karlılığı"
Range("D29").FormulaR1C1 = "Toplam Değişken Üretim Maliyetleri"
Range("D30").FormulaR1C1 = "Toplam Sabit Üretim Maliyetleri"
Range("D31").FormulaR1C1 = "Toplam Net Katkı Payı"
Range("D32").FormulaR1C1 = "Faaliyet Kaldıraç Derecesi"
Range("D33").FormulaR1C1 = "Finansal Kaldıraç Derecesi"
Range("D34").FormulaR1C1 = "Bileşik Kaldıraç Derecesi"
Range("D35").FormulaR1C1 = "Toplam Kaynak"
Range("D36").FormulaR1C1 = "Kısa Vadeli Kredi"
Range("D37").FormulaR1C1 = "Orta Vadeli Kredi"
Range("D38").FormulaR1C1 = "Ödenmiş Sermaye"
Range("D39").FormulaR1C1 = "Kapasite Değişikliği Oranı"
Range("D40").FormulaR1C1 = "Kısa Vadeli Kredi Faiz ve Masraf Oranı"
Range("D41").FormulaR1C1 = "Orta Vadeli Kredi Faiz ve Masraf Oranı"
Range("D42").FormulaR1C1 = "Kontrol"
Range("E4").FormulaR1C1 = "Capacity Change (Kapasite Değişikliği)"
Range("E5").FormulaR1C1 = "Before (Öncesi)"
Range("E6").FormulaR1C1 = a
Range("E7").FormulaR1C1 = b
Range("E8").FormulaR1C1 = c
Range("E9").FormulaR1C1 = "=+R[-1]C+R[-2]C"
Range("E10").FormulaR1C1 = e
Range("E11").FormulaR1C1 = f
Range("E12").FormulaR1C1 = "=+R[-2]C-R[-1]C"
Range("E13").FormulaR1C1 = "=+R[-4]C+R[-1]C"
Range("E14").FormulaR1C1 = "=+R[-8]C-R[-1]C"
Range("E15").FormulaR1C1 = j
Range("E16").FormulaR1C1 = k
Range("E17").FormulaR1C1 = l
Range("E18").FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
Range("E19").FormulaR1C1 = "=+R[-5]C-R[-1]C"
Range("E20").FormulaR1C1 = "=+R[16]C*R[20]C"
Range("E21").FormulaR1C1 = "=+R[16]C*R[20]C"
Range("E22").FormulaR1C1 = "=+R[-1]C+R[-2]C"
Range("E23").FormulaR1C1 = "=+R[-4]C-R[-1]C"
Range("E24").FormulaR1C1 = s
Range("E25").FormulaR1C1 = "=+R[-2]C+R[-1]C"
Range("E26").FormulaR1C1 = "=R[-1]C*0.4"
Range("E27").FormulaR1C1 = "=+R[-2]C-R[-1]C"
Range("E28").FormulaR1C1 = "=R[-5]C/R[10]C"
Range("E29").FormulaR1C1 = "=+R[-22]C+IF(R[-20]C=0,0,(R[-17]C*R[-22]C/R[-20]C))"
Range("E30").FormulaR1C1 = "=+R[-17]C-R[-1]C"
Range("E31").FormulaR1C1 = "=+R[-25]C-R[-2]C"
Range("E32").FormulaR1C1 = "=IF(R[-13]C=0,0,R[-1]C/R[-13]C)"
Range("E33").FormulaR1C1 = "=IF(R[-10]C=0,1,R[-14]C/R[-10]C)"
Range("E34").FormulaR1C1 = "=+R[-1]C*R[-2]C"
Range("E35").FormulaR1C1 = ac
Range("E36").FormulaR1C1 = ad
Range("E37").FormulaR1C1 = ae
Range("E38").FormulaR1C1 = "=R[-3]C-R[-2]C-R[-1]C"
Range("E39").FormulaR1C1 = ag
Range("E40").FormulaR1C1 = ah
Range("E41").FormulaR1C1 = ai
Range("E42").FormulaR1C1 = "=R[-14]C[2]/R[-8]C-R[-3]C"
Range("F5").FormulaR1C1 = "After (Sonrası)"
Range("F6").FormulaR1C1 = "=+RC[-1]*(1+R[33]C[-1])"
Range("F7").FormulaR1C1 = "=+RC[-1]*(1+R[32]C[-1])"
Range("F8").FormulaR1C1 = "=+RC[-1]"
Range("F9").FormulaR1C1 = "=+R[-1]C+R[-2]C"
Range("F10").FormulaR1C1 = "=+RC[-1]"
Range("F11").FormulaR1C1 = "=-R[1]C[-1]*(1+R[-2]C[1])+R[-1]C"
Range("F12").FormulaR1C1 = "=+R[-2]C-R[-1]C"
Range("F13").FormulaR1C1 = "=+R[-4]C+R[-1]C"
Range("F14").FormulaR1C1 = "=+R[-8]C-R[-1]C"
Range("F15").FormulaR1C1 = "=+RC[-1]"
Range("F16").FormulaR1C1 = "=+RC[-1]"
Range("F17").FormulaR1C1 = "=+RC[-1]"
Range("F18").FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
Range("F19").FormulaR1C1 = "=+R[-5]C-R[-1]C"
Range("F20").FormulaR1C1 = "=+RC[-1]"
Range("F21").FormulaR1C1 = "=+RC[-1]"
Range("F22").FormulaR1C1 = "=+R[-1]C+R[-2]C"
Range("F23").FormulaR1C1 = "=+R[-4]C-R[-1]C"
Range("F24").FormulaR1C1 = "=+RC[-1]"
Range("F25").FormulaR1C1 = "=+R[-2]C+R[-1]C"
Range("F26").FormulaR1C1 = "=R[-1]C*0.4"
Range("F27").FormulaR1C1 = "=+R[-2]C-R[-1]C"
Range("F28").FormulaR1C1 = "=R[-5]C/R[10]C[-1]"
Range("F29").FormulaR1C1 = "=+R[-22]C+IF(R[-20]C=0,0,(R[-17]C*R[-22]C/R[-20]C))"
Range("F30").FormulaR1C1 = "=+R[-17]C-R[-1]C"
Range("F31").FormulaR1C1 = "=+R[-25]C-R[-2]C"
Range("G5").FormulaR1C1 = "Rate (Oranı)"
Range("G6").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G7").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G8").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G9").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G10").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G11").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G12").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G13").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G14").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G15").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G16").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G17").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G18").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G19").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G20").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G21").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G22").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G23").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G24").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G25").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G26").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G27").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G28").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G29").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G30").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("G31").FormulaR1C1 = "=IF(RC[-2]=0,0,(RC[-1]-RC[-2])/RC[-2])"
Range("I4").FormulaR1C1 = "Before Capaciti Change"
Range("I5").FormulaR1C1 = "Resources"
Range("I6").FormulaR1C1 = Kay1
Range("I9").FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
Range("I11").FormulaR1C1 = "i"
Range("I12").FormulaR1C1 = "ii"
Range("I13").FormulaR1C1 = "iii = i * ii"
Range("I14").FormulaR1C1 = "iv"
Range("I15").FormulaR1C1 = "v = iii * iv"
Range("I16").FormulaR1C1 = "vi = i + v"
Range("J5").FormulaR1C1 = "Use"
Range("J6").FormulaR1C1 = Use1
Range("J7").FormulaR1C1 = Use2
Range("J8").FormulaR1C1 = Use3
Range("J9").FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
Range("J11").FormulaR1C1 = "Operating Profit / Loss [Before Capaciti Change]"
Range("J12").FormulaR1C1 = "Capacity Change Rate"
Range("J13").FormulaR1C1 = "Operating Profit / Loss Change [1]"
Range("J14").FormulaR1C1 = Kon1
Range("J15").FormulaR1C1 = "Operating Profit / Loss Change [2]"
Range("J16").FormulaR1C1 = "Operating Profit / Loss [After Capaciti Change]"
Range("L4").FormulaR1C1 = "After Capaciti Change"
Range("L5").FormulaR1C1 = "Resources"
Range("L6").FormulaR1C1 = Kay2
Range("L9").FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
Range("M5").FormulaR1C1 = "Use"
Range("M6").FormulaR1C1 = Use4
Range("M7").FormulaR1C1 = Use5
Range("M8").FormulaR1C1 = Use6
Range("M9").FormulaR1C1 = "=+R[-1]C+R[-2]C+R[-3]C"
Range("M11").FormulaR1C1 = "=+R[-5]C[-3]"
Range("M12").FormulaR1C1 = "=+R[27]C[-8]"
Range("M13").FormulaR1C1 = "=R[-1]C*R[-2]C"
Range("M14").FormulaR1C1 = Kon2
Range("M15").FormulaR1C1 = "=+R[-1]C*R[-2]C"
Range("M16").FormulaR1C1 = "=+R[-1]C+R[-5]C"
Range("B2").FormulaR1C1 = Etiket
Columns("A:A").ColumnWidth = 1
Columns("H:H").ColumnWidth = 1
Columns("B:B").ColumnWidth = 12
Columns("C:C").ColumnWidth = 42
Columns("D:D").ColumnWidth = 42
Columns("E:F").ColumnWidth = 16
Columns("G:G").ColumnWidth = 12
Columns("I:J").ColumnWidth = 18
Columns("L:M").ColumnWidth = 18
Columns("K:K").ColumnWidth = 1
Rows("3:3").RowHeight = 6
Call MergeAlan1("B4:D5")
Call MergeAlan2("E4:G4")
Call NormalAlan("E5:G5")
With Range("B4:G5")

.Font.Bold = True
With .Interior

.ColorIndex = 34
.Pattern = xlSolid

End With

End With
Call LineTip1("B4:G42")
Call LineTip1("E4:G42")
Call LineTip1("B6:G31")
Call LineTip1("E6:G31")
Call MergeAlan2("F32:G42")
Range("G6:G31").NumberFormat = "0.00%"
Range("E32:E34").NumberFormat = "#,##0.0000"
Range("E35:E38").NumberFormat = "#,##0.00"
Range("E39:E42").NumberFormat = "0.00%"
Range("E6:E27").NumberFormat = "#,##0.00"
Range("E28").NumberFormat = "0.00%"
Range("E29:E31").NumberFormat = "#,##0.00"
Range("F6:F27").NumberFormat = "#,##0.00"
Range("F28").NumberFormat = "0.00%"
Range("F29:F31").NumberFormat = "#,##0.00"
Call MergeAlan2("I4:J4")
Call MergeAlan2("L4:M4")
Call NormalAlan("I5:J5")
Call NormalAlan("L5:M5")
With Range("I4:J5")

.Font.Bold = True
With .Interior

.ColorIndex = 34
.Pattern = xlSolid

End With

End With
With Range("L4:M5")

.Font.Bold = True
With .Interior

.ColorIndex = 34
.Pattern = xlSolid

End With

End With
Call LineTip1("I4:J9")
Call LineTip1("I4:J5")
Call LineTip1("L4:M9")
Call LineTip1("L4:M5")
Range("I6:J9").NumberFormat = "#,##0.00"
Range("L6:M9").NumberFormat = "#,##0.00"
Call MergeAlan1("I6:I8")
Call MergeAlan1("L6:L8")
With Range("I9:J9")

.Interior.ColorIndex = 34
.Interior.Pattern = xlSolid
.Font.Bold = True

End With
With Range("L9:M9")

.Font.Bold = True
With .Interior

.ColorIndex = 34
.Pattern = xlSolid

End With

End With
Range("M11").NumberFormat = "#,##0.00"
Range("M12").NumberFormat = "0.00%"
Range("M13").NumberFormat = "#,##0.00"
Range("M14").NumberFormat = "#,##0.0000"
Range("M15:M16").NumberFormat = "#,##0.00"
Call LineTip1("I11:M16")
Call LineTip1("M11:M16")
Call MergeAlan2("B2:M2")
With Range("B2:M2")

With .Font

.Bold = True
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic

End With
With .Interior

.ColorIndex = 11
.Pattern = xlSolid

End With
.Font.ColorIndex = 2
.Interior.ColorIndex = 5

End With
Call UnProtectAlan("E6:E8")
Call UnProtectAlan("E10:E11")
Call UnProtectAlan("E15:E17")
Call UnProtectAlan("E24")
Call UnProtectAlan("E35:E37")
Call UnProtectAlan("E39:E41")
Range("I11:M11").Font.Bold = True
Range("I12:M12").Font.Bold = True
Range("I14:M14").Font.Bold = True
Range("I16:M16").Font.Bold = True
With Range("J6").Interior

.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = 46

End With
With Range("M11").Interior

.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = 46

End With
With Range("M6").Interior

.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = 32

End With
With Range("M16").Interior

.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = 32

End With
Call MergeAlan3("J11:L11")
Call MergeAlan3("J12:L12")
Call MergeAlan3("J13:L13")
Call MergeAlan3("J14:L14")
Call MergeAlan3("J15:L15")
Call MergeAlan3("J16:L16")
Call Terazile
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Function
Private Function NormalAlan(Adres As String)

On Error Resume Next
With Range(Adres)

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False

End With

End Function
Private Function MergeAlan1(Adres As String)

On Error Resume Next
With Range(Adres)

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
.Merge

End With

End Function
Private Function MergeAlan2(Adres As String)

On Error Resume Next
With Range(Adres)

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Merge

End With

End Function
Private Function MergeAlan3(Adres As String)

On Error Resume Next
With Range(Adres)

.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Merge

End With

End Function
Private Function UnProtectAlan(Adres As String)

On Error Resume Next
With Range(Adres)

With .Font

.Name = "Arial"
.FontStyle = "Normal"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 32

End With
.Locked = False
.FormulaHidden = False

End With

End Function
Private Function LineTip1(Adres As String)

On Error Resume Next
With Range(Adres)

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium

End With

End Function
Private Function LineTip2(Adres As String)

On Error Resume Next
With Range(Adres)

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium

End With

End Function
Private Sub Terazile()

On Error Resume Next
ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 858.75, 102#, 15#, 8.25).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.IncrementLeft 6.75
Selection.ShapeRange.IncrementLeft 1.5
ActiveSheet.Shapes.AddShape(msoShapeIsoscelesTriangle, 1065#, 102#, 14.25, 9#).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 8
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.IncrementLeft 6.75
Selection.ShapeRange.IncrementLeft 0.75

 End Sub

 

Hiç yorum yok:

Blog Arşivi

Bu gadget'ta bir hata oluştu

Bu Blogda Ara

Contributor

Contributor
Mustafa ULUSARAÇ İstanbul, TÜRKİYE
free counters
T. C. Central Bank Indicative Exchange Rates
Currency Exchange Rate Widget,Currency Converter Widget
Borsa İstanbul