Macro to place a formula into clipboard?

Y

yankee428

Active Member

Joined

Apr 12, 2004

Messages

348

• Jan 26, 2005

• #2

you can do this through a sub procedure:

Code:

Sub test()
ActiveCell.Copy
End Sub

But I am almost positive that you can not do this through a function.

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• Jan 26, 2005

• #3

I wasn't very clear. Sorry.

I don't want the macro to copy a cell but to 'put' a formula into the clipboard. The formula is in the macro of course and not copied from anywhere else.

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Jan 26, 2005

• #4

Try this,

Code:

Private Sub CommandButton1_Click()
Dim MyData As Object
Set MyData = New DataObject
MyData.SetText ActiveCell.Formula
MyData.PutInClipboard
MsgBox MyData.GetText
End Sub

kind regards,
Erik

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Jan 26, 2005

• #5

FinancialAnalystKid,
sorry I didn't see your last post, but it's easy to change the macro

Code:

Private Sub CommandButton1_Click() 
Dim MyData As Object 
Set MyData = New DataObject 
MyData.SetText "=TabName()"
MyData.PutInClipboard 
MsgBox MyData.GetText 
End Sub

will this help?
Erik

 

Upvote 0

Y

yankee428

Active Member

Joined

Apr 12, 2004

Messages

348

• Jan 26, 2005

• #6

Oh, ok. I don't know how to copy VBA code through code, my guess is that you would have to build the formula as an object then copy the object. But how about if we use some blank real estate on your file:

Code:

Sub test()
Range("$AA$1").Value = "=20+sum($D$2)" 'replace with your formula
Range("$AA$1").Copy
End Sub

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• Jan 26, 2005

• #7

erik.van.geit said:

FinancialAnalystKid,
sorry I didn't see your last post, but it's easy to change the macro

Code:

Private Sub CommandButton1_Click() 
Dim MyData As Object 
Set MyData = New DataObject 
MyData.SetText "=TabName()"
MyData.PutInClipboard 
MsgBox MyData.GetText 
End Sub

will this help?
Erik

Click to expand...

Does this have to be in a workbook code or as a macro? Can this just be a public command?

 

Upvote 0

Y

yankee428

Active Member

Joined

Apr 12, 2004

Messages

348

• Jan 26, 2005

• #8

Very cool Erik!

Finance Kid,
Yes, just drop the code into a sub like this.

Code:

Sub formula2clipboard()
Dim MyData As Object
Set MyData = New DataObject
MyData.SetText "=TabName()" 'change to whatever your formula is
MyData.PutInClipboard
MsgBox MyData.GetText
End Sub

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Jan 26, 2005

• #9

Does this have to be in a workbook code or as a macro? Can this just be a public command?

Click to expand...

I wrote it as a test with a button, but you can do this in any module.

Sub put_in_clipboard()
........ (code form previous post)
End Sub

kind regards,
Erik

EDIT: it's strange: I didn't see yankee428's reply when clicking to REPLY myself

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Jan 26, 2005

• #10

JFYI - the DataObject is in the MSForms library. So you need to have a reference to that in your project in order for this to work.

Regards,

 

Upvote 0