No announcement yet.

Regression in VBA

  • Filter
  • Time
  • Show
Clear All
new posts

  • Regression in VBA


    FYI: A sample of some of the data I am working with is attached.

    I used VBA a couple times in school and didn't really understand much, so I basically only know that it exists. I hope someone can help me with an assignment I have for work, because I'm definitely struggling. I've done the necessary analysis in Minitab, but now my supervisor wants a program in Excel since he doesn't know how to use Minitab. So here it goes....

    This has to do with doing regression analysis to determine the variance in different accounts based upon numerous variables. First, I have monetary figures (dependant variable) for three years, listed by month under each account name. I also have six possible independant variables also listed by month.

    I am hoping to do the following:
    1. Have the ability to add data at the end of every month to the columns.
    2. Be able to pick the dates (from-to) to use in the regression (combo box).
    3. Be able to pick the independant variables to use in the regression (from just one to all in combo box).
    4. The regression needs to be able to exclude the variables that don't have a p-value of at least 90% significance.
    5. Be able to pick the dependant variable to be analyzed (ability to select multiple variables at once, resulting in multiple outputs would be ideal).
    6. I need the following regression statistics to be displayed for each analysis: Adjusted R2, F-value with conditional formatting, F significance level, and independant variable p-value.

    I know that the chance of getting all this is slim, but does anyone at least have some advice on how to get started or where else to look?


    desperate newbie
    Attached Files
    Last edited by willie447; October 26th, 2005, 07:21.

  • #2
    Re: Regression in VBA


    Have a look at the attached. It may go some way to meeting some of your requirements.

    Press the button, select the date range, make multiple selections from the Independent variable list, and one selection from the dependent list.

    It should copy across to the sheet Regression, the relevant data, then perform Excel's standard regression.

    At this stage, it will only allow selection of one dependent....

    See if that will help.
    Attached Files


    • #3
      Re: Regression in VBA

      Thanks for the help. I'm having a little trouble though. I get an error stating "Object variable or With block variable not set" and when I go to the debug screen, the following code is highlighted:

      startrow = Sheets("data").Range("a:a").Find(what:=Format(ComboBox1.Value, "mmm-yy")).Row
      lastrow = Sheets("data").Range("a:a").Find(what:=Format(ComboBox2.Value, "mmm-yy")).Row

      Any help is appreciated!!! Thanks!


      • #4
        Re: Regression in VBA


        Worked yesterday, but not today. Try this one.

        Attached Files


        • #5
          Re: Regression in VBA

          it works!! Thanks!!


          • #6
            Re: Regression in VBA

            Hi Ozgrid;

            I tried runing your Regression VBA Examplev2; but unfortunetly, I keep getting an error at the floolwing line below:
            findit = WorksheetFunction.Match(Sheets("regression").Cells(1, i).Value, arr, 0)

            Error read 'Unable to get the Match of the worksheetFunction class'

            Any idea as to why this is happening?


            • #7

              Re: Regression in VBA

              Hi Lesliev,
              Welcome to the forum. Please do not post questions in other peoples threads. This is known as thread hijacking. Please start your own thread for this question with a meaningful thread title. You can create a link back to this thread if you think it will be useful. Also can you add code tags to all code in your posts
              If the solution helped please donate to RSPCA

              Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman