需要使用VBA函数指导在Excel中粘贴值 [英] Need guidance with a VBA function to paste values in Excel

问题描述

我是VBA新手，但已成功创建了一些有用的Excel函数。我现在正在工作的那个，看起来应该很简单，正在逃避我。我认为我误解了语法，需要一些指导。

考虑以下屏幕截图;我试图在列E中创建函数，它只是D $ n中的VALUE。

所以远，这是我得到的：

 函数PASTVALUE（q As String）
 q.PasteSpecial粘贴：= xlPasteValues 
 End Function 
  

如果我理解正确，值（在我的情况下，单元格的内容 D $ n ）作为字符串，然后使用 PasteValues 来粘贴。

在粘贴之前，我需要以某种方式复制它吗？我认为 q As String 参数是将它带入函数的。

但是如果我不复制，它是否试图从一个空的剪贴板中粘贴粘贴 ... in哪些情况下我有 no 想法我应该用来完成此操作。

帮助！

解决方案

您可以像这样转移数值（显示）

  Function PASTEVALUE（rng As Range）
 PASTEVALUE = rng.Text 
 End Function 
  

或使用评估（）函数来评估该范围内的公式

 函数PASTEVALUE（rng与范围）
 PASTEVALUE = [rng] 
结束函数
  

I'm a VBA newbie, but have successfully created a handful of useful Excel Functions. The one I'm working on right now, which seems like it should be simple, is eluding me. I think I'm misunderstanding the syntax, and need some guidance.

Consider the following screen capture; I am attempting to create the function in Column E, which is simply the VALUE from D$n.

So far, this is as far as I've gotten:

Function PASTVALUE(q As String)
q.PasteSpecial Paste:=xlPasteValues
End Function

which, if I understand properly, is reading the input value (in my case, the contents of cell D$n) as a String, then pasting it using PasteValues.

Do I need to somehow copy it before I paste it? I thought that the q As String parameter was what brought it into the function.

But then if I'm not copying, is it trying to paste from an empty clipboard...in which case I have no idea what I should be using to accomplish this.

Help!

解决方案

You can just ''transfer'' the value(displayed) over like this

Function PASTEVALUE(rng As Range)
    PASTEVALUE = rng.Text
End Function

or use the Evaluate() function to evaluate the formula in that range

 Function PASTEVALUE(rng As Range)
        PASTEVALUE = [rng]
    End Function

这篇关于需要使用VBA函数指导在Excel中粘贴值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！