当前位置：文江博客话题详情

在 Excel 用户定义函数中使用工作表

发布于 2024-10-11 06:33:18 字数 1263 浏览 8 评论 0原文

我试图编写的 VBA 相当简单，但我从未编写过 VBA，并且来自 Visual Studio & C#世界，这真是地狱！！

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

alt text

所以我有两张重要的表格。范围表每个日期有 2 个值。它需要一个结果，计算表采用两个值并给我一个结果。

我想将每个日期的当前值和一年值放入计算表中，并将结果放入结果列中。

所以我尝试定义一个 UDF，但后来我了解到我无法修改 UDF 中的工作表内容。

我尝试录制一个宏，然后希望从那里开始，但我得到了这样的结果：

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

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

有人能让我朝正确的方向前进吗？

注意：大约有 2000 多个日期。

希望我的问题很清楚！

更新我还要澄清一些事情：

我严重简化了 Calc 工作表，实际上，它需要 5 个值（最多有 6 年的列）然后该工作表调用大约 4 个其他工作表并执行一些操作一些不懂技术的人写的财务计算。

我试图让财务人员用代数向我表达他在做什么，这样我就可以编写一个 C# 函数。

我也尝试编写 UDF，但就像我说的那样，它需要放入值并获取结果，这意味着 UDF 将修改工作表。

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

基本上我对学习/理解 VBA 语法和 API 感到头疼。

我正在寻找一个小例子，说明如何将一张纸中的值放入另一张纸中，然后取回其他一些值？也许在循环中！？

原文

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

alt text

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!?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沧桑㈠ 2024-10-18 06:33:18

您可能使事情变得比您需要的更复杂。作为一名程序员，我相信您会意识到，在学习新系统时，花一点时间借助 Google、“傻瓜”书籍等是值得的。 Excel 强调一种与 C# 等截然不同的编程风格。

因此，这里有一些选项，从最简单、最惯用的 Excel 开始：

1) 在结果列中使用公式。如果您想要进行的计算足够简单，那么这就是正确的方法。举一个简单的例子，假设您的计算只是将两个数字相加。您可以将公式放入

=B2+C2

单元格 E2 中，然后将其拖到 E 列中。 Excel 会知道要做什么并为 E3=B3+C3 等制作论坛。

仅使用公式您就可以做很多事情。 Excel 具有许多内置的工作表函数，您可以在网络和书店中找到大量材料。

2) 用VBA编写UDF并从结果列中调用它。 UDF 是一种不会修改工作表中其他任何内容的函数。它只是根据输入参数计算结果。当公式中的某些内容过于复杂而无法执行时，或者想要在多个位置使用公式时，您可以编写 UDF。（请注意，将论坛拖过某个范围是 Excel 惯用的做法，出于 DRY 目的，也算作“一个地方”）。

因此，要编写 UDF（让我们使用加法示例，但您会编写更复杂的逻辑），您需要将这样的函数放入 VBA 代码模块中：

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

然后将公式放入

=myUDF(B2, C2)

单元格 E2 中并将其拖动沿着E栏。

请注意，在本例中我忽略了诸如错误处理之类的内容。这就是公式比 UDF 更“Excel”的原因之一。现在您必须开始编写与 Excel 的声明式函数风格不同的代码，这是一种不同的思维模型。

编辑：
2.5) 使用 Excel 数据表 - 很遗憾我之前忘记了这一点，但是如果您恰好有一个或两个单元格是您的独立参数，并且恰好有一个单元格是您的结果，那么Excel 有一个内置的“假设”分析工具可供您使用。 “数据表”这个名字令人遗憾，因为这两个词的含义都太重了，但这就是它的名字。您告诉 Excel 您的输入和输出是什么，它就会执行您想要的操作。请参阅：

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

这是其中的小介绍关联：

数据表是一系列单元格，
显示如何改变一两个
公式中的变量（公式：A
值序列、单元格引用、
中的名称、函数或运算符
细胞共同产生新的
价值。公式总是以
等号 (=).) 将影响
这些公式的结果。数据表
提供计算的捷径
一次操作中有多个结果，并且
查看和比较结果的方法
所有不同的变化
一起记录在工作表上。

3) 编写一个宏，它像函数一样使用计算表。如果该工作表上的逻辑非常复杂，并且转换为 VBA 太困难，因为它一直在变化，或者因为它是由不了解 VBA 且不熟悉的 Excel 用户开发的，那么您可能会这样做使用命令式编程。我不会提供代码，但伪代码可能如下所示：

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

这现在与“Excel 方式”相去甚远，因为您必须自己设置数据，然后运行宏。如果您返回并更改某些“输入”，则需要重新运行宏。（使用工作表事件，这可以在某种程度上自动化。）

很多刚接触 Excel 的程序员都会直接跳到这一步，将 Excel 降级为一个巨大的网格控件，而忽略了它最初存在的原因 - 自动重新计算。

编辑以响应有问题的其他信息：

好的，看起来情况 (3) 是合适的，因为您有一个复杂的工作表，不容易手动转换为 VBA 或 C# 代码。下面是一些（过度简化、硬编码、缺少 EH 等）代码，可让您更多地了解如何在 VBA 中执行此操作：

首先，有一个 VBA 宏，它假装它是一个 UDF。请注意，它是一个 Sub，而不是一个函数，并且您不能从公式中调用它。您可以调用子例程来响应用户执行的操作，例如选择菜单选项或按命令按钮：

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

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

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

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

这是您为响应用户操作或从 Worksheet_Change 事件等而调用的内容。会很慢而且笨重，而且我省略了很多实用的 VBA 内容，但它应该足以让您入门。（我使用“RangeSheet”而不是“Range”作为工作表代码名称，以避免与 Excel 定义的 Range 类和 Application.Range 方法发生冲突。）

4) 使用一些第三方工具将您的工作表转换为 UDF，然后您可以像 (2) 中那样调用它。有一些商业工具可以获取您的工作表，让您指定哪些单元格是输入和输出，然后生成 C++ 代码或任何可以编译到 XLL 插件中的代码。

5) 等待 Microsoft 执行此操作：

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

6) 使用解析器一：

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

有一项功能允许您将整个工作簿用作另一个工作簿中的函数，或者我认为是在任何 Python 代码中。

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.

回复收藏 0 原文

‘画卷フ 2024-10-18 06:33:18

您以“相对”模式录制了宏。 R[-8]C[-10]表示：当前单元格上方8行、左侧10列的单元格。

回复收藏 0 原文

柒夜笙歌凉 2024-10-18 06:33:18

在这种情况下，通常最好在 Excel 范例中工作，而不是转换为 C# 方式：
不知道为什么您需要 UDF 而不是财务人员编写的 Excel 公式：为什么您/财务人员不能编写一些公式来将输入提供给财务人员工作表，然后再编写一些公式来提供结果后退？
如果您确实需要使用 VBA 执行此操作，那么 J Tolle 已经为您提供了重复循环的基本架构，但我建议您首先考虑使用公式执行此操作。

回复收藏 0 原文

~没有更多了~