Macro to place a formula into clipboard?

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
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().
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
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.
 

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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
 

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
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
 

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779

ADVERTISEMENT

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

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

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top