Paste Sum as Value

[Kaspars]

Hi,

Have one old custom made (not by myself) macro which copies Sum of selected cells and then puts it into clipboard. It works on my machine, but won't work copied to another excel. Here's the code

>>> use code - tags <<<

Sub StatusBar_Sum()
    Dim sTemp As String
    Dim R As Range
    Dim f As Variant
    Dim obj As New DataObject

    Set R = Selection
    Set f = Application.WorksheetFunction

    sTemp = sTemp & f.Sum(R) & vbCrLf

    obj.SetText sTemp
    obj.PutInClipboard
End Sub

First error was in "Dim obj As New DataObject", I changed it to "Dim obj As Object" and then got run-time error 91 on this line "obj.SetText sTemp". And now I'm confused why this macro still works in my Excel but not in another.

Any ideas how to fix this?

 

[Marc L]

Hi, back to the original code (should be done with 4 codelines only just removing the useless variables !)​

then do not forget to activate Microsoft Forms 2.0 within References of VBA Tools menu !​

If it is already activated then check for any 'missing' reference …​

​

 

[p45cal]

You might also get away without setting that reference by using:

Sub StatusBar_Sum()
Dim sTemp As String
Dim R As Range
Dim f As Variant
Dim obj

Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Set R = Selection
Set f = Application.WorksheetFunction

sTemp = sTemp & f.Sum(R) & vbCrLf

obj.SetText sTemp
obj.PutInClipboard
End Sub

 

[Kaspars]

You might also get away without setting that reference by using:

Sub StatusBar_Sum()
Dim sTemp As String
Dim R As Range
Dim f As Variant
Dim obj

Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Set R = Selection
Set f = Application.WorksheetFunction

sTemp = sTemp & f.Sum(R) & vbCrLf

obj.SetText sTemp
obj.PutInClipboard
End Sub

Thank You very much! This made the trick!

 

[Kaspars]

Hi, back to the original code (should be done with 4 codelines only just removing the useless variables !)​

then do not forget to activate Microsoft Forms 2.0 within References of VBA Tools menu !​

If it is already activated then check for any 'missing' reference …​

​

Although @p45cal version worked, I wonder where I can get the MS Forms 2.0? 'cause I couldn't locate it within References.

 

[p45cal]

where I can get the MS Forms 2.0? 'cause I couldn't locate it within References.

If, in your project, you add a userform, then straightaway delete it again, you'll have that reference ticked in Tools|References for you.

 

[Marc L]

I wonder where I can get the MS Forms 2.0? 'cause I couldn't locate it within References.

OS issue …​

​