Macro to place a formula into clipboard?

[FinancialAnalystKid]

Is it possible to create a macro to put a formula into the clipboard?

So with the next [enter] or [ctrl][v] it pastes into any selected cell?

Say the formula is a UDF like =TabName().

 

[yankee428]

you can do this through a sub procedure:

Sub test()
ActiveCell.Copy
End Sub

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

 

[FinancialAnalystKid]

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.

 

[erik.van.geit]

Try this,

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

 

[erik.van.geit]

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

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

 

[yankee428]

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:

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

 

[FinancialAnalystKid]

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

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

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

 

[yankee428]

Very cool Erik!

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

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

 

[erik.van.geit]

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

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

 

[Greg Truby]

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,