需要帮助使用Excel函数在VBA子 [英] Need help using Excel functions in VBA sub

问题描述

所以我正在做这些功课，完全没有把Excel公式放在我的代码中。这是提示：

3. 在代码中使用Excel函数Application.WorksheetFunction）来计算
   的统计信息，并将相应的值分配给类型为double的变量。引用
   范围名称ScoresData时，引用作为参数
   作为函数提供的范围。记住 - 在代码中使用Excel函数时，需要使用VB语法完成范围
   规范。




4. 打开一个消息框，显示存储在变量中的值（参见下面的
   上的图像）。提示：

a。您可以使用常量vbNewLine在提示符中断行。例如：
MsgBoxBIT 311中有这么多的资料vbNewLine& Oich！ -
将在311之后放置换行符。

b。您可以使用常量vbTab在
提示符的两个部分之间添加一个Tab空格。例如：MsgBoxAverage：& vbTab&平均

5. 最后，将宏分配给工作表上的按钮。

<

我当前的代码（不工作）是：

 code> Sub ScoresStatistics（）
 
 Dim Average As Double 
 Dim StandDev As Double 
 Dim Min As Double 
 Dim Max As Double 
 Dim ScoresData As Range 
 
 
 Range（A1）。End（xlDown）.Name =ScoresData
 
 Average = Application.WorksheetFunction（Average（ScoresData） ）
 StandDev = Application.WorksheetFunction（StDev.P（ScoresData））
 Min = Application.WorksheetFunction（Minimum（ScoresData））
 Max = Application.WorksheetFunction（Maximum（ScoresData））
 
 MsgBox以下是分数的总结措施：& vbNewLine& 平均：& vbTab&平均& vbNewLine& 标准偏差：& vbTab& StandDev& vbNewLine& 最小：& vbTab&最小& vbNewLine& 最大：& vbTab& Max 
 
 End Sub 
  

解决方案

这是一个相当合理的第一次尝试，但我发现你正在混合不同的方法来实现同样的事情。使用一种方法的一半，另一种方法的一半很少得到正确的结果。通常，程序员会倾向于使用一种方法。这并不意味着其他方法是错误的或低效的;只要记住一种方法的语法和过程，而不是在类似的方法之间弹起来，并且可能会混淆语法，就像这样在这里完成的那样简单。

它看起来像你想要处理一系列的单元格，如果你在A1并点击了 Ctrl + Shift + ▼。您可以a）命名范围，b）将范围分配给范围变量，c）直接处理编码范围定义。以下是这三种方法的一些例子（还有其他方法）。 Debug.Print 行将范围的地址输出到VBE的立即窗口。使用 Ctrl + G 或查看►立即窗口查看输出。

 'a）命名范围。 
'之后，您可以通过参考Range（ScoresData）
 Range（A1：A& Range（A1））使用范围。End（xlDown）.row）.Name =ScoresData
 Debug.Print范围（ScoresData）。地址
 
'b）将范围分配给范围类型变量。 
'之后，您可以通过引用rScoresData 
使用范围$ Dim $ rScoresData As Range 
设置rScoresData = Range（Range（A1），Range（A1）。End（xlDown ））
 Debug.Print rScoresData.Address 
 
'c）有很多方法直接引用范围
'这里有几个。 
 Debug.Print Range（A1：A& Range（A1）。End（xlDown）.row）.Address 
 Debug.Print Range（Range（A1），Range A1）。End（xlDown））。Address 
 Debug.Print Cells（1，1）.Resize（Cells（1，1）.End（xlDown）.row，1）.Address 
 Debug.Print Cells（1，1）.Resize（Cells（Rows.Count，1）.End（xlUp）.row，1）.Address 
  

从即时窗口输出可以看出，所有这些都完全一样。选择其中一个并记住如何使用它。至少现在，你可以忘记所有其他人，但你应该重新审视他们，并知道他们的工作方式，以防将来遇到他们。

你的创建您提供的代码示例的复制和粘贴/重命名方法令人沮丧。在尝试编写VBA代码以重现其结果之前，您应至少知道正确的Excel工作表函数以及如何在工作表上使用它们。认真;这显示了您的部分明显缺乏的努力。我将提供使用您的一个要求的正确语法， MIN功能。你将不得不研究和调整我所提供的所有休息。这不是学习如何在电子表格的列中获取最小值的地方。还有很多其他网站。这是一个学习如何从工作表中的列获得最小值的代码的地方。

 '声明变量。请注意，我没有使用保留字作为变量的名称
 Dim mn As Double 
 
'a）使用命名范围。 
 mn = Application.WorksheetFunction.Min（Range（ScoresData））
 Debug.Print最小值范围（ScoresData）。地址（0，0，外部：= True）& 是& mn 
 
'b）使用范围类型变量。 
 mn = Application.WorksheetFunction.Min（rScoresData）
 Debug.Print最小值在& rScoresData.Address（0，0，external：= True）& 是& mn 
 
'c）使用全编码范围定义
 mn = Application.WorksheetFunction.Min（Range（A1：A& Range（A1）。End（xlDown） .row））
 Debug.Print最小值在&范围（A1：A& Range（A1）。End（xlDown）.row）.Address（0，0，external：= True）& 是& mn 
  

这应该是足够让你开始的。发回任何问题，注意到示例，错误信息等。

So I'm working through this homework, and am totally stuck at putting Excel formulas in my code. This is the prompt:

3. Use Excel functions in the code (with Application.WorksheetFunction) to calculate the statistics and assign the corresponding values to variables of type double. Use the range name ScoresData when referring to the range which is provided as an argument to the functions. Remember – when using Excel functions in the code, the range specifications need to be done using VB syntax.

4. Open a single message box showing the values stored in the variables (see image on below). Tips:

a. You can use the constant vbNewLine to break a line in the prompt. For example: MsgBox "there is so much material in BIT 311" & vbNewLine & "Oich!" – will place a line break after 311.

b. You can use the constant vbTab to add a Tab space between two parts of the prompt. For example: MsgBox "Average:" & vbTab & Average

5. Finally, assign the macro to a button on the worksheet.

And my current code (not working) is:

Sub ScoresStatistics()

    Dim Average As Double
    Dim StandDev As Double
    Dim Min As Double
    Dim Max As Double
    Dim ScoresData As Range


    Range("A1").End(xlDown).Name = "ScoresData"

    Average = Application.WorksheetFunction(Average(ScoresData))
    StandDev = Application.WorksheetFunction(StDev.P(ScoresData))
    Min = Application.WorksheetFunction(Minimum(ScoresData))
    Max = Application.WorksheetFunction(Maximum(ScoresData))

    MsgBox "Here are summary measures for the scores:" & vbNewLine & "Average:" & vbTab & Average & vbNewLine & "Standard Deviation:" & vbTab & StandDev & vbNewLine & "Minimum:" & vbTab & Min & vbNewLine & "Maximum:" & vbTab & Max

End Sub

解决方案

That's a pretty reasonable first attempt but I find that you are mashing together different methods of achieving the same thing. Using half of one method and half of another rarely gets the correct result. Typically, programmers will favor one method over another. That does not mean that other methods are wrong or inefficient; just that it's easier to remember the syntax and processes from one method rather than bouncing between similar methods and possibly confusing the syntaxes as what looks like was done here.

It looks like you want to deal with a range of cells that would be selected if you were on A1 and tapped Ctrl+Shift+▼. You can a) name the range, b) assign the range to a range variable, c) deal with the coded range definition directly. Here are some examples of those three methods (there are others). The Debug.Print lines output the range's address to the VBE's Immediate Window. Use Ctrl+G or View ► Immediate Window to view the output.

    ' a) Name the range.
    '    After this you can use the range by referring to Range("ScoresData")
    Range("A1:A" & Range("A1").End(xlDown).row).Name = "ScoresData"
    Debug.Print Range("ScoresData").Address

    ' b) Assign the range to a range type variable.
    '    After this you can use the range by referring to rScoresData
    Dim rScoresData As Range
    Set rScoresData = Range(Range("A1"), Range("A1").End(xlDown))
    Debug.Print rScoresData.Address

    ' c) There are lots of ways to refer to the range directly
    '    Here are a few.
    Debug.Print Range("A1:A" & Range("A1").End(xlDown).row).Address
    Debug.Print Range(Range("A1"), Range("A1").End(xlDown)).Address
    Debug.Print Cells(1, 1).Resize(Cells(1, 1).End(xlDown).row, 1).Address
    Debug.Print Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).row, 1).Address

As you can see from the Immediate Window output all of those amount to exactly the same thing. Pick one of those and memorize how to use it. At least for now, you can forget about all of the others but you should revisit them and know how they work just in case you run into them in the future.

Your copy and paste/rename method of creating the code sample you provided was discouraging. You should have at least known the correct Excel worksheet functions and how to use them on a worksheet before attempting to write VBA code to reproduce their results. Seriously; this shows a distinct lack of effort on your part. I'll provide the correct syntax for using one of your requirements, the MIN function. You will have to research and adapt what I'm providing for all of the rest. This isn't the place to learn how to get the minimum value in a column on a spreadsheet. There are lots of other sites for that. This is a place to learn how to write code that gets the minimum from a column on a worksheet.

    ' Declare the variable. Note that I am NOT using a reserved word as the variable's name
    Dim mn As Double

    ' a) Use the Named Range.
    mn = Application.WorksheetFunction.Min(Range("ScoresData"))
    Debug.Print "Minimum value in " & Range("ScoresData").Address(0, 0, external:=True) & " is " & mn

    ' b) Use the Range type variable.
    mn = Application.WorksheetFunction.Min(rScoresData)
    Debug.Print "Minimum value in " & rScoresData.Address(0, 0, external:=True) & " is " & mn

    ' c) Use the full coded range definition
    mn = Application.WorksheetFunction.Min(Range("A1:A" & Range("A1").End(xlDown).row))
    Debug.Print "Minimum value in " & Range("A1:A" & Range("A1").End(xlDown).row).Address(0, 0, external:=True) & " is " & mn

That should be more than enough to get you started. Post back with any questions you have, noting examples, error messages, etc.

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