使用excel用户定义的功能表 [英] using a sheet in an excel user defined function

问题描述

我想写的VBA很简单，但我从来没有写过VBA，而是从视觉工作室和C＃世界，这真的是地狱！

所以我真的很感激这里的任何帮助/指针/提示

所以我有两个重要的表。范围表每个日期有2个值。它需要一个结果，计算表需要两个值，并得到一个结果。

我想把Current和OneYear值 strong>进入计算表，并将结果存入结果列。

所以我尝试定义一个UDF，但是后来我学到了，我无法修改UDF中的表格内容。 / p>

我尝试录制一个宏，然后希望从那里开始，但我有这样的东西：

<$ p $选择
表格（24Feb05-24FEB10）选择
范围（K9）。选择
ActiveCell.FormulaR1C1 = = Calc！R [-8] C [-10]
Range（K9）。选择

假设R1C1是行和列，但是为什么地球上它说-8和-10！？

有人可以让我在右边方向？

注意：约有2000多个日期。

希望我的问题清楚！ >

---

更新
我会清除更多的东西：

Im 粗略地简化了Calc表，实际上它需要5个值（最多有六个列），然后该表再调用其他4个表，财务计算，一些非技术精湛的家伙写道。

我试图让金融家在代数中向我表达他在做什么，所以我可以写一个C＃函数。

我也尝试写UDF，但是像我说的那样，需要在放入值，并获得结果，这意味着UDF将修改表。

 函数Func1（txt As Variant）As String 
 //从分隔的文本字符串返回第n个元素
 Dim txt As String 
 txt = text 
 Sheet4.Cells（1，1）= txt 
 Func1 = Sheet4.Cells（2，1）//获取结果值
结束函数
  

基本上，我有一个头痛学习/了解VBA语法和API。

我正在寻找一个小例子，我将如何将值从一张纸到另一张，然后再获得一些其他的价值？也许在循环！

解决方案

您可能会使这种情况比您需要更复杂。作为一名程序员，我相信你们在学习新系统的同时，有一点帮忙，Google，Dummies书籍等都花费了很多时间。 Excel表示与C＃不同的编程风格。

所以，这里有一些选项，从最简单和最大的Excel语言开始：

1）在结果列中使用公式。如果你想要做的计算是简单的，这是要走的路。作为一个简单的例子，说你的计算只是添加两个数字。你可以使用公式

  = B2 + C2 
  

进入单元格E2，然后将其拖到E列。 Excel将会知道该怎么做，并为E3 = B3 + C3等创建论坛。

您可以用公式做很多事情。 Excel具有许多内置的工作表功能，您可以在网页和书店找到大量的材料。

2）在VBA中编写UDF ，并从结果列中调用它。 UDF是一种功能，不会在工作表中修改任何其他内容。它只是根据其输入参数计算结果。当你在一个公式中有一些太复杂的东西，或者当你想在多个地方使用公式时，你会写一个UDF。 （请注意，在一个Range范围内拖动一个forumla是习惯的Excel，并将其作为DRY目的的一个地方）。

所以，要写一个UDF（让我们使用加法例如，但是您会写更复杂的逻辑），您可以将这样的函数放入VBA代码模块中：

 公共功能myUDF（当前，一年）
 myUDF =当前+一年
结束功能
  

然后你可以使用公式

  = myUDF（B2，C2）
  

进入单元格E2，并将其拖动到E列。

请注意，被忽略的东西像这个例子中的错误处理。这就是为什么公式比UDF更Excel的原因之一。现在你必须开始编写代码，从Excel的声明功能性样式开始，这是一个不同的心理模型。

编辑：
2.5）使用Excel数据表 - 我很尴尬，我以前忘记了这一点，但如果你有一个或两个单元格是你的独立参数，而一个单元格是你的结果，那么Excel有一个内置的 what-if分析工具。名称数据表是令人遗憾的，因为这两个字都是如此重载，但这就是所谓的。你告诉Excel你的输入和输出是什么，它会做你想要的。请参阅：

http://office.microsoft.com/en-us/excel-help/calculate-multiple-results-by-using-a-data-table- HP010072656.aspx

以下是该链接的小介绍：

数据表是一系列单元格，
显示如何更改公式中的一个或两个
变量（公式：A
值序列，单元格引用，
名称一个
值的
单元格中的函数或函数，一个公式总是以
等号（=）开头）会影响这些
的结果公式。数据表
提供一个快捷方式来计算
在一个操作中的多个结果和
a方法来查看和比较工作表上所有不同变体
的结果
。

3）编写一个宏，使用您的计算表，就像这是一个功能。如果您在该工作表上有非常复杂的逻辑，那么您可能会执行此操作，并且转换成VBA太难了，因为它一直更改，或者是由不知道VBA而不熟悉的Excel用户开发的与命令式编程。我不会提供代码，但伪代码可能如下所示：

  Public Sub doCalc（）
 '对于结果列中的每个单元格
'将相应的当前和单元格单元格复制到计算单
'Excel将重新计算计算表
'将计算表上的结果单元格复制回范围表在正确的位置
'end 
 End Sub 
  

这是现在远离Excel方式，因为您必须自己设置数据，然后运行该宏。如果您返回并更改某些输入，则需要重新运行宏。 （这可以在一定程度上自动化，使用Worksheet事件。）

很多新的Excel程序员跳到这一步，将Excel降级到一个巨大的电网控制并忽略了它首先存在的原因 - 自动重新计算。

在问题中对附加信息进行编辑：

好的，它看起来像case（3）是合适的，因为你有一个复杂的表不容易手动翻译成VBA或C＃代码。这里有一些（过于简单，硬编码，缺少EH等）代码，让您更了解如何在VBA中执行此操作：

首先，假装是UDF的VBA宏。请注意，它是一个Sub，而不是一个函数，并且您不要从公式中调用它。您调用子程序以响应用户做某事，例如选择菜单选项或按命令按钮：

  Public Sub fakeFunct（input1 ，input2，outputRng As Range）
 CalcSheet。[b1] = input1 
 CalcSheet。[b2] = input2 
 
'Excel重新定义单元格B3中的内容，如果您设置为自动重新计算
 
 outputRng = CalcSheet。[b3] 
 End Sub 
  

在此示例中，CalcSheet是工作表模块的代码名称。 （有关更多信息，请参阅Chip Pearson的优秀网站： http://www.cpearson.com/excel /codemods.htm ）

您实际上可以从另一个循环访问输入的宏调用上述宏：

  Public Sub loopFakeFunct（）
 Dim i As Long 
 For i = 2 To 2000 
调用fakeFunct（RangeSheet.Cells （i，2），RangeSheet.Cells（i，3），RangeSheet.Cells（i，5））
 Next i 
 End Sub 
  

这是您响应用户操作或从Worksheet_Change事件等呼叫的事情。它会缓慢而笨重，我要离开很多实用的VBA的东西，但它应该足以让你开始。 （我使用'RangeSheet'而不是'Range'作为片段代码名称，以避免与Range定义的Range类和Application.Range方法冲突。）

4 ）使用一些第三方工具，将工作表转换为UDF，然后可以像（2）那样调用。那里有一些商业工具，可以拿你的工作表，让你指定哪些单元格是输入和输出，然后生成C ++代码或者可以编译成XLL加载项的任何东西。

5）等待Microsoft 执行此操作：

http://research.microsoft.com/en-us/um/people/simonpj/papers/excel/excel .htm

6）使用Resolver One ：

< a href =http://www.resolversystems.com/products/resolver-one/ =nofollow> http://www.resolversystems.com/products/resolver-one/

它具有一个功能，允许您将整个工作簿用作另一个功能，或者我认为任何Python代码。

The VBA I'm trying to write is fairly simple but Ive never written VBA and coming from the visual studio & C# world, this truly is hell!!

So I'll really be grateful for any help/pointers/hints here

So I have two important sheets. The Range sheet has 2 values per date. It needs a result, The Calc sheet takes two values and gets me a result.

I want to Put the Current, and OneYear values for each date into the Calc sheet and get the result into the result column.

So I tried defining a UDF but I learned later I cannot modify sheet contents in a UDF.

I tried recording a macro and then hoped to go from there, but I got something like this:

Range("A2").Select
Sheets("24Feb05-24FEB10").Select
Range("K9").Select
ActiveCell.FormulaR1C1 = "=Calc!R[-8]C[-10]"
Range("K9").Select

Im assuming R1C1 is Row and Column but why on earth does it say -8 and -10!??

Can someone get me in the right direction?

Note: There are about 2000+ dates.

Hope my question is clear!

---

Update I'll clear up a few more things:

Im grossly simplifying the Calc sheet, in reality, it needs 5 values (there are columns upto SixYears) And then that sheet calls about 4 other sheets and does some finance calculations that some non-tech-savvy guy wrote.

I tried to get the finance guy to express to me in algebra what he's doing, so I could just write up a C# function.

I also tried writing a UDF, but like I said it needs to put in the values and get the result, which means the UDF will modify the sheet.

Function Func1(txt As Variant) As String
//Returns the nth element from a delimited text string
    Dim txt As String
    txt = text
   Sheet4.Cells(1, 1) = txt
   Func1 = Sheet4.Cells(2, 1)//get the result value'
End Function

Basically Im having a head ache learning/understand VBA syntax and the API.

Im looking for maybe a small example of how I would take values from one sheet into another and then get the some other values back? Maybe in a loop!?

解决方案

You might be making this more complicated than you need to. As a programmer, I'm sure you appreciate that when learning a new system, a little time with the help, Google, "Dummies" books, etc. is time well spent. Excel emphasizes a very different programming style than, say, C#.

So, here are some options, starting with the simplest and most Excel-idiomatic:

1) Use a formula in your result column. If the calculation you want to do is simple enough, this is the way to go. As a trivial example, say your calculation is just adding the two numbers. You'd put the formula

=B2+C2

into cell E2, and then drag it down the E column. Excel will know what to do and make forumlas for E3=B3+C3 and so on.

You can do quite a lot with just formulas. Excel has many built-in worksheet functions, and you can find tons of material on the web and in the bookstore.

2) Write a UDF in VBA and call it from the result column. A UDF is a function that doesn't modify anything else in the worksheet. It just calculates a result based on its input parameters. You would write a UDF when you have something too complex to do in a formula, or when you want to use the formula in multiple places. (Note that dragging a forumla across a Range is idiomatic Excel and counts as "one place" for DRY purposes).

So, to write a UDF (let's use the addition example, but you'd write more complex logic), you'd put a function like this into a VBA code module:

Public Function myUDF(current, oneYear)
    myUDF = current + oneYear
End Function

You'd then put the formula

=myUDF(B2, C2)

into cell E2 and drag that down the E column.

Note that I've neglected stuff like error handling in this example. This is one reason why formulas are more "Excel" than UDFs. Now you have to start writing code that departs from Excel's declarative functional style, and it's a different mental model.

EDIT: 2.5) Use an Excel Data Table - I'm embarrased that I forgot this before, but if you have exactly one or two cells that are your independent parameters, and exactly one cell that is your result, then Excel has a built-in "what-if" analysis tool that you can use. The name "data table" is regrettable since both of those words are so heavily overloaded, but that's what it's called. You tell Excel what your input(s) and output are, and it will do what you want. See:

http://office.microsoft.com/en-us/excel-help/calculate-multiple-results-by-using-a-data-table-HP010072656.aspx

Here is the little intro from that link:

A data table is a range of cells that shows how changing one or two variables in your formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet.

3) Write a macro that uses your calc sheet like it was a function. You might do this if you've got really complex logic on that sheet, and it's too hard to convert to VBA because it changes all the time or because it's being developed by an Excel user who doesn't know VBA and isn't familiar with imperative programming. I'm not going to supply code, but the pseudocode might look like this:

Public Sub doCalc()
    'for each cell in the result column
        'copy the corresponding current and oneYear cells to the calc sheet
        'Excel will recalc the calc sheet
        'copy the result cell on the calc sheet back to the Range sheet in the right place
    'end
End Sub

This is now departing pretty far from the "Excel way", since you have to set up your data yourself, and then run the macro. If you go back and change some of the "inputs", you need to re-run the macro. (This can be automated to some extent, using Worksheet events.)

Lot's of programmers who are new to Excel jump right to this step, relegating Excel to a giant grid control and neglecting the reason it exists in the first place - automatic recalculation.

EDIT IN RESPONSE TO ADDITIONAL INFO IN QUESTION:

O.K., it looks like case (3) is appropriate because you have a complex sheet not easily manually translated into VBA or C# code. Here is some (oversimplified, hardcoded, lacking EH, etc.) code to give you more of an idea of how to do this in VBA:

First, there is the VBA macro that pretends it's a UDF. Note that it is a Sub, not a function, and you don't call it from a formula. You call subroutines in response to the user doing something like choosing a menu option or pressing a command button:

Public Sub fakeFunct(input1, input2, outputRng As Range)
    CalcSheet.[b1] = input1
    CalcSheet.[b2] = input2

    'Excel recalcs what's in cell B3 if you have it set to automatic recalcualation

    outputRng = CalcSheet.[b3]
End Sub

In this example, 'CalcSheet' is the "code name" of your worksheet module. (For more about this, see Chip Pearson's excellent website: http://www.cpearson.com/excel/codemods.htm)

You'd actually call the above macro from another one that loops through your "inputs":

Public Sub loopFakeFunct()
    Dim i As Long
    For i = 2 To 2000
        Call fakeFunct(RangeSheet.Cells(i, 2), RangeSheet.Cells(i, 3), RangeSheet.Cells(i, 5))
    Next i
End Sub

This is the thing you call in response to user action, or from the Worksheet_Change event, etc. It will be slow and clunky, and I'm leaving a lot of practical VBA stuff out, but it should be enough to get you started. (I used 'RangeSheet' instead of 'Range' as a sheet code name to avoid conflicts with the Range class and the Application.Range method defined by Excel.)

4) Use some third-party tool that converts your worksheet into a UDF that you can then call as in (2). There are commerical tools out there that will take your sheet, let you specifiy which cells are inputs and outputs, and then generate C++ code or whatever that can be compiled into an XLL add-in.

5) Wait for Microsoft to do this:

http://research.microsoft.com/en-us/um/people/simonpj/papers/excel/excel.htm

6) Use Resolver One:

http://www.resolversystems.com/products/resolver-one/

It has a feature that allows you to use a whole workbook as a function in another one, or I think in any Python code.

这篇关于使用excel用户定义的功能表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！