How can I convert this Excel VB code to Access VB 
Author Message
 How can I convert this Excel VB code to Access VB

Currently, I use the following process:

1) Gather some queries in Access
2) Output them to Excel
3) Run my code on Each file (There are 12 of them)
4) Collect the results (2 numbers per file)
5) Manually put them into another Access Table.

The reason I do my calc's in Excel rather than Access is that I don't know
how to execute VB on Access query data. Therefore, I use Excel to reference
particular cell values. What I'd like to do is have a control /menu item
somewhere that when chosen will select all of the queries, perform the
calculations using the data, then automatically create the table.

Each of the queries I use has 3 columns, equal in size (within a query, but
not across queries).
In Excel, Row 1 represents the headers.The first column represents a month
#, the other two, y and x values.
I calculate slope (m) and intercept (b) for a least-squares regression,
eliminating outlier data.
In the code below, I just save the end values to a pair of cells. I'd like
to write them to a table automatically though in form: Month #, Slope,
Intercept.

Any thoughts on how to convert this?

Marston

Sub CalcRegress()
    Dim b() As Double, m() As Double
    Dim sxy1 As Double, sxy2 As Double
    Dim sxy3 As Double, sxx2 As Double
    Dim ma As Double, ba As Double, bd As Double, md As Double
    Dim mt As Double, bt As Double, m2 As Double, n2 As Double
    Dim mf As Double, bf As Double, diff As Double
    Dim count As Integer, ncount As Integer
    Range("B2").Select <---- Row 1 contains headers
    count = Range(ActiveCell, ActiveCell.End(xlDown)).Cells.count  <----This
would equal # in query column
    ReDim b(count)
    ReDim m(count)
    For j = 1 To count
        sxy1 = 0
        sxy2 = 0
        sxy3 = 0
        sxx1 = 0
        sxx2 = 0
    For i = 1 To count
    If i <> j Then
        sxy1 = sxy1 + Cells(i + 1, 2).Value * Cells(i + 1, 3).Value  <------
how would I refer to individual query data
        sxy2 = sxy2 + Cells(i + 1, 2).Value
        sxy3 = sxy3 + Cells(i + 1, 3).Value
        sxx1 = sxx1 + Cells(i + 1, 3).Value * Cells(i + 1, 3).Value
    End If
    Next i
        m(j) = ((count - 1) * sxy1 - sxy2 * sxy3) / ((count - 1) * sxx1 -
sxy3 * sxy3)
        b(j) = (sxy2 * sxx1 - sxy3 * sxy1) / ((count - 1) * sxx1 - sxy3 *
sxy3)
    Next j
        ma = 0
        ba = 0
        md = 1
        bd = 1
        mt = 0
        bt = 0
        m2 = 0
        b2 = 0
        For l = 1 To count
            mt = mt + m(l)
            bt = bt + b(l)
            m2 = m2 + m(l) * m(l)
            b2 = b2 + b(l) * b(l)
        Next l
        ma = mt / count
        ba = bt / count
        md = sqr((count * m2 - mt * mt)/(count*(count-1)))
        bd = sqr((count * b2 - bt * bt)/(count*(count-1)))
        sxy1 = 0
        sxy2 = 0
        sxy3 = 0
        sxx1 = 0
        sxx2 = 0
        For n = 1 To count
        diff = Abs((m(n) - ma) / md) / 2 + Abs((b(n) - ba) / bd) / 2
        If diff <= 2 Then
        sxy1 = sxy1 + Cells(n + 1, 2).Value * Cells(n + 1, 3).Value
        sxy2 = sxy2 + Cells(n + 1, 2).Value
        sxy3 = sxy3 + Cells(n + 1, 3).Value
        sxx1 = sxx1 + Cells(n + 1, 3).Value * Cells(n + 1, 3).Value
        ncount = ncount + 1
        End If
        Next
        mf = ((ncount) * sxy1 - sxy2 * sxy3) / ((ncount) * sxx1 - sxy3 *
sxy3)
        bf = (sxy2 * sxx1 - sxy3 * sxy1) / ((ncount) * sxx1 - sxy3 * sxy3)
        Range("D2").Value = bf  <----Evenutally, rather than stamp these
into cells, I'd pass them back through the
        Range("E2").Value = mf       -subroutine, collecting each pair for
each query I process, putting into a table.
        Application.ScreenUpdating = True
End Sub



Sun, 25 Aug 2002 03:00:00 GMT  
 How can I convert this Excel VB code to Access VB
Why not store the values from the Access queries, which you are passing to Excel at present, in a VBA array? This will allow you to
operate on the array elements just as you manipulate the Excel Range item values?

| Currently, I use the following process:
|
| 1) Gather some queries in Access
| 2) Output them to Excel
| 3) Run my code on Each file (There are 12 of them)
| 4) Collect the results (2 numbers per file)
| 5) Manually put them into another Access Table.
|
| The reason I do my calc's in Excel rather than Access is that I don't know
| how to execute VB on Access query data. Therefore, I use Excel to reference
| particular cell values. What I'd like to do is have a control /menu item
| somewhere that when chosen will select all of the queries, perform the
| calculations using the data, then automatically create the table.
|
| Each of the queries I use has 3 columns, equal in size (within a query, but
| not across queries).
| In Excel, Row 1 represents the headers.The first column represents a month
| #, the other two, y and x values.
| I calculate slope (m) and intercept (b) for a least-squares regression,
| eliminating outlier data.
| In the code below, I just save the end values to a pair of cells. I'd like
| to write them to a table automatically though in form: Month #, Slope,
| Intercept.
|
| Any thoughts on how to convert this?
|
| Marston
|
|
|
|
| Sub CalcRegress()
|     Dim b() As Double, m() As Double
|     Dim sxy1 As Double, sxy2 As Double
|     Dim sxy3 As Double, sxx2 As Double
|     Dim ma As Double, ba As Double, bd As Double, md As Double
|     Dim mt As Double, bt As Double, m2 As Double, n2 As Double
|     Dim mf As Double, bf As Double, diff As Double
|     Dim count As Integer, ncount As Integer
|     Range("B2").Select <---- Row 1 contains headers
|     count = Range(ActiveCell, ActiveCell.End(xlDown)).Cells.count  <----This
| would equal # in query column
|     ReDim b(count)
|     ReDim m(count)
|     For j = 1 To count
|         sxy1 = 0
|         sxy2 = 0
|         sxy3 = 0
|         sxx1 = 0
|         sxx2 = 0
|     For i = 1 To count
|     If i <> j Then
|         sxy1 = sxy1 + Cells(i + 1, 2).Value * Cells(i + 1, 3).Value  <------
| how would I refer to individual query data
|         sxy2 = sxy2 + Cells(i + 1, 2).Value
|         sxy3 = sxy3 + Cells(i + 1, 3).Value
|         sxx1 = sxx1 + Cells(i + 1, 3).Value * Cells(i + 1, 3).Value
|     End If
|     Next i
|         m(j) = ((count - 1) * sxy1 - sxy2 * sxy3) / ((count - 1) * sxx1 -
| sxy3 * sxy3)
|         b(j) = (sxy2 * sxx1 - sxy3 * sxy1) / ((count - 1) * sxx1 - sxy3 *
| sxy3)
|     Next j
|         ma = 0
|         ba = 0
|         md = 1
|         bd = 1
|         mt = 0
|         bt = 0
|         m2 = 0
|         b2 = 0
|         For l = 1 To count
|             mt = mt + m(l)
|             bt = bt + b(l)
|             m2 = m2 + m(l) * m(l)
|             b2 = b2 + b(l) * b(l)
|         Next l
|         ma = mt / count
|         ba = bt / count
|         md = sqr((count * m2 - mt * mt)/(count*(count-1)))
|         bd = sqr((count * b2 - bt * bt)/(count*(count-1)))
|         sxy1 = 0
|         sxy2 = 0
|         sxy3 = 0
|         sxx1 = 0
|         sxx2 = 0
|         For n = 1 To count
|         diff = Abs((m(n) - ma) / md) / 2 + Abs((b(n) - ba) / bd) / 2
|         If diff <= 2 Then
|         sxy1 = sxy1 + Cells(n + 1, 2).Value * Cells(n + 1, 3).Value
|         sxy2 = sxy2 + Cells(n + 1, 2).Value
|         sxy3 = sxy3 + Cells(n + 1, 3).Value
|         sxx1 = sxx1 + Cells(n + 1, 3).Value * Cells(n + 1, 3).Value
|         ncount = ncount + 1
|         End If
|         Next
|         mf = ((ncount) * sxy1 - sxy2 * sxy3) / ((ncount) * sxx1 - sxy3 *
| sxy3)
|         bf = (sxy2 * sxx1 - sxy3 * sxy1) / ((ncount) * sxx1 - sxy3 * sxy3)
|         Range("D2").Value = bf  <----Evenutally, rather than stamp these
| into cells, I'd pass them back through the
|         Range("E2").Value = mf       -subroutine, collecting each pair for
| each query I process, putting into a table.
|         Application.ScreenUpdating = True
| End Sub
|
|
|



Sun, 25 Aug 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Converting VB Code from Access 97 to Access 2002 or higher

2. Converting VB code modules in Access 95 to Access 97

3. Converting Access 2.0 DB to Access 97 DB in VB code

4. Convert from mdb to mde in vb-code from another access database

5. convert access code to vb

6. Convert MS-Access report to VB code?

7. (Excel) Accessing macro VB code for existing buttons

8. Tell me you experience in convert code from VB 6 to VB.NET or C#

9. converting code from VB 3.0 to VB 5.0

10. Looking for VB code or Access/Excel Applic accounting programing - vbcode.txt [1/1]

11. Exporting data to excel report from access database using vb code

12. Convert VB Automation code into VC++ code

 

 
Powered by phpBB® Forum Software