[belajar-excel] MENGUBAH FORMULA KE TEKS

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] MENGUBAH FORMULA KE TEKS

Septi Yunita
Dear belajar excel,
Mau Tanya nech.. saya sedang baca-baca buku excel, ada bab tentang mengubah formula ke teks



Missal :
A1=1
A2=2
A3=3
Di A4=sum(A1:A3)

Untuk mengubah formula di A4 menjadi teks dapat menggunakan define name yaitu :
1.insert>nama>define
2.isi name
3.isi refers to dengan :
     =GET.CELL(6,B4)

Pertanyaan saya angka 6 diatas berarti apa ya??

Trims atas bantuan dan penjelasannya


#####################################################################################
This email is confidential and may also be privileged.  If you are not the intended
recipient, please delete it and notify us immediately.  You should not copy or use
it for any purpose, nor disclose its contents to any other person.
#####################################################################################
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] MENGUBAH FORMULA KE TEKS

siti Vi
Administrator
*Kita sedang bicara tentang Makro Excel 4*

Angka 6 berarti ingin menampilkan *FORMULA dlm cell yg dirujuk*

argument GET CELL ada 60 lebih, (pernah ditayangkan di milis ini lengkap)
ini siti pilihkan sebaign yg penting penting

  *Proposed Name*

*Arg #*

*What =GET.CELL(Arg #,INDIRECT("RC[-1]",)) will return*

AbsReference

1

Absolute style reference like [Book1.xls]Sheet1!$A$1

ShowValue

5

Cell value

ShowFormula

6

Cell formula

NumFormat

7

Number format of cell

IsLocked

14

True if cell is locked

FormulaHidden

15

True if cell formula is hidden

ShowWidth

16

Cell width. If array-entered into two cells of a row, second value is true
if width is standard

ShowHeight

17

Cell height

WorkbookName

32

Workbook name like [Book1.xls]Sheet1 or Book1.xls if workbook and single
sheet have identical names

ShowFormulaWOT

41

Cell formula without translation into language of workspace

HasNote

46

True if cell has a text note

HasFormula

48

True if cell contains a formula

IsArray

49

True if cell is part of an array formula

IsStringConst

52

Text alignment char ' if cell is a string constant, empty string if not

AsText

53

Cell displayed as text with numbers formatted and symbols included

WorksheetName

62

Worksheet name like [Book1.xls]Sheet1

WorkbookName

66

Workbook name like Book1.xls



2011/6/16 Septi Yunita <[hidden email]>

>
>
>  Dear belajar excel,
>
> Mau Tanya nech.. saya sedang baca-baca buku excel, ada bab tentang mengubah
> formula ke teks
>
>
>
> Missal :
>
> A1=1
>
> A2=2
>
> A3=3
>
> Di A4=sum(A1:A3)
>
>
>
> Untuk *mengubah formula di A4 menjadi teks* dapat menggunakan define name
> yaitu :
>
> 1.insert>nama>define
>
> 2.isi name
>
> 3.isi refers to dengan :
>
> *     =GET.CELL(6,B4)*
>
> Pertanyaan saya angka* 6* diatas berarti apa ya??
>
>
>
> Trims atas bantuan dan penjelasannya
>
~siti
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] MENGUBAH FORMULA KE TEKS

Septi Yunita



o… sudah jelas kalau begitu,

trims mbak atas penjelasannya…




From: [hidden email] [mailto:[hidden email]] On Behalf Of STDEV(i)
Sent: Thursday, June 16, 2011 2:37 PM
To: [hidden email]
Subject: Re: [belajar-excel] MENGUBAH FORMULA KE TEKS


Kita sedang bicara tentang Makro Excel 4

Angka 6 berarti ingin menampilkan FORMULA dlm cell yg dirujuk

argument GET CELL ada 60 lebih, (pernah ditayangkan di milis ini lengkap)
ini siti pilihkan sebaign yg penting penting

Proposed Name

Arg #

What =GET.CELL(Arg #,INDIRECT("RC[-1]",)) will return

AbsReference

1

Absolute style reference like [Book1.xls]Sheet1!$A$1

ShowValue

5

Cell value

ShowFormula

6

Cell formula

NumFormat

7

Number format of cell

IsLocked

14

True if cell is locked

FormulaHidden

15

True if cell formula is hidden

ShowWidth

16

Cell width. If array-entered into two cells of a row, second value is true if width is standard

ShowHeight

17

Cell height

WorkbookName

32

Workbook name like [Book1.xls]Sheet1 or Book1.xls if workbook and single sheet have identical names

ShowFormulaWOT

41

Cell formula without translation into language of workspace

HasNote

46

True if cell has a text note

HasFormula

48

True if cell contains a formula

IsArray

49

True if cell is part of an array formula

IsStringConst

52

Text alignment char ' if cell is a string constant, empty string if not

AsText

53

Cell displayed as text with numbers formatted and symbols included

WorksheetName

62

Worksheet name like [Book1.xls]Sheet1

WorkbookName

66

Workbook name like Book1.xls




2011/6/16 Septi Yunita <[hidden email]<mailto:[hidden email]>>

Dear belajar excel,
Mau Tanya nech.. saya sedang baca-baca buku excel, ada bab tentang mengubah formula ke teks

Missal :
A1=1
A2=2
A3=3
Di A4=sum(A1:A3)

Untuk mengubah formula di A4 menjadi teks dapat menggunakan define name yaitu :
1.insert>nama>define
2.isi name
3.isi refers to dengan :
     =GET.CELL(6,B4)
Pertanyaan saya angka 6 diatas berarti apa ya??

Trims atas bantuan dan penjelasannya



#####################################################################################
This email is confidential and may also be privileged.  If you are not the intended
recipient, please delete it and notify us immediately.  You should not copy or use
it for any purpose, nor disclose its contents to any other person.
#####################################################################################

image001.jpg (494 bytes) Download Attachment
image002.jpg (456 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Re: MENGUBAH FORMULA KE TEKS

sudarsono-2

Sedikit info tambahan untuk excel 2007/2010 ( excel 2003 belum saya coba
) , maka macronya :
     Range("b6").Select
     a = Excel.ExecuteExcel4Macro("get.cell(6)")
     Range("c6").Value = "'" & a


'======


--- In [hidden email], Septi Yunita <septi.yunita@...>
wrote:

>
>
>
>
> o… sudah jelas kalau begitu,
>
> trims mbak atas penjelasannya…
>
>
>
>
> From: [hidden email]
[mailto:[hidden email]] On Behalf Of STDEV(i)

> Sent: Thursday, June 16, 2011 2:37 PM
> To: [hidden email]
> Subject: Re: [belajar-excel] MENGUBAH FORMULA KE TEKS
>
>
> Kita sedang bicara tentang Makro Excel 4
>
> Angka 6 berarti ingin menampilkan FORMULA dlm cell yg dirujuk
>
> argument GET CELL ada 60 lebih, (pernah ditayangkan di milis ini
lengkap)

> ini siti pilihkan sebaign yg penting penting
>
> Proposed Name
>
> Arg #
>
> What =GET.CELL(Arg #,INDIRECT("RC[-1]",)) will return
>
> AbsReference
>
> 1
>
> Absolute style reference like [Book1.xls]Sheet1!$A$1
>
> ShowValue
>
> 5
>
> Cell value
>
> ShowFormula
>
> 6
>
> Cell formula
>
> NumFormat
>
> 7
>
> Number format of cell
>
> IsLocked
>
> 14
>
> True if cell is locked
>
> FormulaHidden
>
> 15
>
> True if cell formula is hidden
>
> ShowWidth
>
> 16
>
> Cell width. If array-entered into two cells of a row, second value is
true if width is standard

>
> ShowHeight
>
> 17
>
> Cell height
>
> WorkbookName
>
> 32
>
> Workbook name like [Book1.xls]Sheet1 or Book1.xls if workbook and
single sheet have identical names

>
> ShowFormulaWOT
>
> 41
>
> Cell formula without translation into language of workspace
>
> HasNote
>
> 46
>
> True if cell has a text note
>
> HasFormula
>
> 48
>
> True if cell contains a formula
>
> IsArray
>
> 49
>
> True if cell is part of an array formula
>
> IsStringConst
>
> 52
>
> Text alignment char ' if cell is a string constant, empty string if
not

>
> AsText
>
> 53
>
> Cell displayed as text with numbers formatted and symbols included
>
> WorksheetName
>
> 62
>
> Worksheet name like [Book1.xls]Sheet1
>
> WorkbookName
>
> 66
>
> Workbook name like Book1.xls
>
>
>
>
> 2011/6/16 Septi Yunita septi.yunita@...<mailto:septi.yunita@...>
>
> Dear belajar excel,
> Mau Tanya nech.. saya sedang baca-baca buku excel, ada bab tentang
mengubah formula ke teks
>
> Missal :
> A1=1
> A2=2
> A3=3
> Di A4=sum(A1:A3)
>
> Untuk mengubah formula di A4 menjadi teks dapat menggunakan define
name yaitu :

> 1.insert>nama>define
> 2.isi name
> 3.isi refers to dengan :
> =GET.CELL(6,B4)
> Pertanyaan saya angka 6 diatas berarti apa ya??
>
> Trims atas bantuan dan penjelasannya
>
>
>
>
########################################################################\
#############
> This email is confidential and may also be privileged. If you are not
the intended
> recipient, please delete it and notify us immediately. You should not
copy or use
> it for any purpose, nor disclose its contents to any other person.
>
########################################################################\
#############
>


Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Re: MENGUBAH FORMULA KE TEKS

siti Vi
Administrator
kalau  sudah terlanjur masuk ke VBA, menurut pendapatku :
ya  tidak ferlu repot-repot memakai perintah *makro excel-4* lagi..
Lebih mudah membuat fungsi sendiri, nantinya di worksheet akan lebih
fleksibel

Public Function FormulaText(X as Range,) As String
*   FormulaTex = X.Formula*
End Function

Di worksheet, UDF tsb bisa ditulis di mana saja; misal cell yg mau di rujuk
D6
( formula yg ada di cell D6 akan dimunculkan di L54) , maka di L54 tinggal
tulis
=FormulaText(D6)

Ini lebih praktis jika dibandingkan dgn Define Name yg memanfaatkan
Get.Cell
ex makro Excel4, karena letak dan jarak dari cell yg akan dirujuk  harus
tertentu

Misal A1 hanya bisa dimunculkan formulanya di B1 (sesuai define name nya)
Jika A1 dingin dimunculkan formrulanya di  C1 kita harus mengubah Define
Namenya dulu



On Thu, Jun 16, 2011 at 5:02 PM, sudarsono <[hidden email]> wrote:

>
>
> Sedikit info tambahan untuk excel 2007/2010 ( excel 2003 belum saya coba )
> , maka macronya :
>     Range("b6").Select
>     a = *Excel.ExecuteExcel4Macro("get.cell(6)")*
>     Range("c6").Value = "'" & a
>
> '======
>
>
> --- In [hidden email], Septi Yunita <septi.yunita@...>
> wrote:
> > o… sudah jelas kalau begitu,
> > trims mbak atas penjelasannya…
>
>
~siti
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] Re: MENGUBAH FORMULA KE TEKS

Septi Yunita
Mbak siti trims atas masukannya, ini saya coba UDF-nya tapi kok tidak memberikan hasil?
Salahnya di mana ya??


Trims sebelumnya



From: [hidden email] [mailto:[hidden email]] On Behalf Of STDEV(i)
Sent: Friday, June 17, 2011 6:31 AM
To: [hidden email]
Subject: Re: [belajar-excel] Re: MENGUBAH FORMULA KE TEKS



kalau  sudah terlanjur masuk ke VBA, menurut pendapatku :
ya  tidak ferlu repot-repot memakai perintah makro excel-4 lagi..
Lebih mudah membuat fungsi sendiri, nantinya di worksheet akan lebih fleksibel

Public Function FormulaText(X as Range,) As String
   FormulaTex = X.Formula
End Function

Di worksheet, UDF tsb bisa ditulis di mana saja; misal cell yg mau di rujuk D6
( formula yg ada di cell D6 akan dimunculkan di L54) , maka di L54 tinggal tulis
=FormulaText(D6)

Ini lebih praktis jika dibandingkan dgn Define Name yg memanfaatkan Get.Cell
ex makro Excel4, karena letak dan jarak dari cell yg akan dirujuk  harus tertentu

Misal A1 hanya bisa dimunculkan formulanya di B1 (sesuai define name nya)
Jika A1 dingin dimunculkan formrulanya di  C1 kita harus mengubah Define Namenya dulu



On Thu, Jun 16, 2011 at 5:02 PM, sudarsono <[hidden email]<mailto:[hidden email]>> wrote:


Sedikit info tambahan untuk excel 2007/2010 ( excel 2003 belum saya coba ) , maka macronya :
    Range("b6").Select
    a = Excel.ExecuteExcel4Macro("get.cell(6)")
    Range("c6").Value = "'" & a

'======

--- In [hidden email]<mailto:[hidden email]>, Septi Yunita <septi.yunita@...> wrote:
> o… sudah jelas kalau begitu,
> trims mbak atas penjelasannya…



#####################################################################################
This email is confidential and may also be privileged.  If you are not the intended
recipient, please delete it and notify us immediately.  You should not copy or use
it for any purpose, nor disclose its contents to any other person.
#####################################################################################

image001.jpg (456 bytes) Download Attachment
image002.jpg (494 bytes) Download Attachment
Book1.xls (47K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Re: MENGUBAH FORMULA KE TEKS

siti Vi
Administrator
salahnya di makronya  FormulaText   tertulis FormulaTex


Public Function FormulaText(X As Range) As String
   Formula*Text* = X.Formula
End Function


tertulisnya
Public Function FormulaText(X As Range) As String
   Formula*Tex** *= X.Formula
End Function




2011/6/17 Septi Yunita <[hidden email]>

>  Mbak siti trims atas masukannya, ini saya coba UDF-nya tapi kok tidak
> memberikan hasil?
>
> Salahnya di mana ya??
>
>
>
> Trims sebelumnya
>
>
>
~siti