none
Visual Basic syntax error in statistical measurements using excel RRS feed

  • Question

  • Hi guys,

    I am very new in coding in Microsoft visual basic and I am having some syntax errors in printing out some results on a data table.

    The code I have written is as follows:

      For I = 1 To 10000
        SumSq = SumSq + (RUC(I) - AverageResinUnitCost) ^ 2
      Next I
      '
      Variance = SumSq / (9999#)
      StandardDeviation = Sqr(Variance)
      '
      'Worksheets("User Interface").Cells(IRow, 5) = Variance
      'Worksheets("User Interface").Cells(IRow, 6) = StandardDeviation
      '
      'Close #6
    End Function

    The lines I commented out in the end are the lines that don't seem to be executing when I run the command. The Variance and Standard Deviation values are supposed to appear in the 5th and 6th columns of my data table but they don't. I was wondering if any of you know the syntax issue in my statement and could help me solve this problem. Also I am using Excel 2013 btw. Thanks!



    • Edited by A1994RA Thursday, June 16, 2016 12:43 PM
    Wednesday, June 15, 2016 3:06 PM

Answers

  • Either create 3 distinct functions that calculate each measure or create a sub procedure that when you run, calculate everything you want. Put a button in the worksheet so when you click, the calculation happens.

    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by A1994RA Thursday, June 16, 2016 2:13 PM
    Thursday, June 16, 2016 1:56 PM

All replies

    1. What does (9999#) signify or mean?
    2. What value has IRow?
    3. Why don't you use Option Explicit and declare your variables?
    4. End Function has no matching Function

    Best regards, George

    Wednesday, June 15, 2016 3:15 PM
  • Well, an error message for those lines would help, don't you think?

    Best regards, George

    Wednesday, June 15, 2016 3:49 PM
  • Hi,

    I am unsure about how to view the error messages for this code when I run it on excel. All I see so far is that when I run the code the values on the table don't pop up where I want them too. I don't yet know how to get the program to display the specific error messages. 

    Thanks.

    Wednesday, June 15, 2016 5:38 PM
  • Also to clarify the value of IRow, I just wanted to note that its value is specified in the cell where the call to the AverageResinUnitCost function is made, so the variable only appears in the function header and in the last couple lines of code where its value is specified. Please let me know if this helps a bit more in terms of understanding the problem. Thanks!
    Wednesday, June 15, 2016 5:48 PM
  • The sintax is not wrong.

    If you have a worksheet called User Interface in the active workbook and IRow is greater then 0, you shouldn't get an error.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, June 15, 2016 6:03 PM
  • Originally, without running the code wit the values of Standard deviation specified I get the following result:

    However when I try specifying the values for columns 5 and 6 with the variance and standard deviation using the above mentioned lines, I get this:

    Is it possible there is some other structural part of the code that I will need to look at to solve this. Because the error seems to only occur when I try and include the specification of the variance and standard deviation values so I assumed the syntax of those lines is what is throwing my code off. Thanks!

    Wednesday, June 15, 2016 6:14 PM
  • Please note that the original picture is the result when I comment out specifying the variance and standard deviation values.
    Wednesday, June 15, 2016 6:16 PM
  • Hi A1994RA,

    I try to test your code and find that the code you had provided in that code there is no use of parameters that you had taken in the function.

    its just using the numeric values to perform some arithmetical operation.

    could you please provide us a value that you had assigned to them and which data type you had declared for them.

    it is more helpful if you post a full function code so that we can know the functionality of it and we can understand what is the role of these lines in your code.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 16, 2016 2:40 AM
    Moderator
  • Hi Deepak,

    I have included a simplified function of the part of the code that seems to be giing problems and the numbers I am using in the codes statement:

    Var and StD have been defined as Double and Variant types with no resolution of the write to cell issue (red code does not execute).
    
    SumSq = 0#
      '
      For i = 1 To 10000
        SumSq = SumSq + (X(i) - XBar) ^ 2
      Next i
      '
      Var = SumSq / (9999#)
      StD = Sqr(Var)
      '
      Worksheets("User").Range("E4").Value = Var
      Worksheets("User").Range("F4").Value = StD
    
    The following substitution with Irow = 4 has also been used with no success:
    
      Worksheets("User").Cells(Irow, 5).Value = Var
      Worksheets("User").Cells(Irow,6).Value = StD
    
    Please let me know if this simplifies the problem for you and if it helps you. Thanks!

    Thursday, June 16, 2016 12:56 PM
  • I had Highlighted the lines,

      Worksheets("User").Range("E4").Value = Var
      Worksheets("User").Range("F4").Value = StD

    in red but it did not show in the code snippet I inserted. Also, feel free to modify the values of X(i) and Xbar to your choosing. Thanks!

    Thursday, June 16, 2016 1:03 PM
  • I am basically just trying to find the variance and standard deviation of a series of data points for some reason when I try and place the Var and StdDev values at a specific cell on the excel chart it doesn't work.
    Thursday, June 16, 2016 1:14 PM
  • Now it is clear.

    You are using your function in a worksheet formula.

    Excel has a limitation: when you use a function as a worksheet formula, you can't change the value of other cells (in fact, you can't change the value of any excel object).

    UDF (user defined functions) are supposed to RETURN a function value and that value will be returned to the cell as the result.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, June 16, 2016 1:29 PM
  • Alright, 

    Thanks, so what would be the best solution for me to get the variance and standard deviation standard deviation values on the excel chart then? 

    Thursday, June 16, 2016 1:40 PM
  • Either create 3 distinct functions that calculate each measure or create a sub procedure that when you run, calculate everything you want. Put a button in the worksheet so when you click, the calculation happens.

    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by A1994RA Thursday, June 16, 2016 2:13 PM
    Thursday, June 16, 2016 1:56 PM
  • Thanks for your help!

    Wish you the best!

    Thursday, June 16, 2016 2:13 PM