Results 1 to 4 of 4
  1. #1
    theracer06 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    7

    From Excel VBA to Access VBA

    Hi,



    I am looking at the transfering the following code from Excel VBA to Access VBA and was wondering if anybody could help me by telling me which factors I should look out for/ how I should change the code so as to make it functional.

    What the code does is to take an interval in a set of numbers and smooth out the peaks and valleys by linearising according to a "Target" smoothing factor, i.e. adding or subtracting to the numbers until the addition and subtractions reach the Target.

    Code:
    Sub test()                                              
    Total = 0
    num_days = Worksheets("run_tool").Cells(12, 5)
    ' interval to run the function on. 1 day means 24 numbers to be smoothed
    
    For hour_num = 0 To num_days * 24 - 1 Step 24
      
        
        tot = 0     
    'total changes made
        Target = Worksheets("run_tool").Cells(8, 5)  
    'smoothing factor, total subtractions/additions equal this number
        inc = Worksheets("run_tool").Cells(9, 5) / 24        
    'step value for change
    Do      
    ' loop to add/subtract till target met
        Max = Worksheets("run_tool").Cells(2 + hour_num, 2)
    'take first cell as start for max
        Min = Worksheets("run_tool").Cells(2 + hour_num, 2) 
    'first cell as start for min
        maxindex = 2 + hour_num                                      
    'points at cell 1 for  max
        minindex = 2 + hour_num                                   
    'points at cell 1 for min
        Change = 0           
    'flag for changes not used at present
            For i = 2 + hour_num To 24 + hour_num       
    ' loop through remainder of list
                x = Worksheets("run_tool").Cells(i + 1, 2)    
    ' pull out value from list - call it x
                If x > Max Then                          
    'check if this is a max
                Max = x
                maxindex = i + 1
            End If
            If x < Min Then                 
    'check if this is a min
                Min = x
                minindex = i + 1
            End If
            Next
            Worksheets("run_tool").Cells(maxindex, 2) = Worksheets("run_tool").Cells(maxindex, 2) - inc   'lower max
            Worksheets("run_tool").Cells(minindex, 2) = Worksheets("run_tool").Cells(minindex, 2) + inc    'increae min
            tot = tot + inc                                     'add to total changes made
    Loop Until (tot >= Target)
    
    'keep going till change is > target change
    Total = Total + tot
    tot = 0
    Worksheets("run_tool").Cells(6, 5) = Total
    Next hour_num
    
    End Sub

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    That's actually a pretty hefty question because it also means you have to worry about transferring the data itself from a spreadsheet to a database. Depending on how the database was designed, the coding could change drastically.

    That said, if you do it right, the only changes you'll need to make would be to any Cell or Worksheet references in the code. Those are the only Excel-specific components to VBA code so everything else should be transferable as-is.

  3. #3
    theracer06 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    7
    Hi Rawb,

    thanks for your reply.

    The database already contains similar data so the only thing I need to transfer is the parameters and the code.

    I thought there would be a different/more efficient way of looping through the dataset with the Access VBA?

  4. #4
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Certainly, there will be different ways of doing it, but since I can't see the data behind the function (and since I don't really get what the purpose of the function is in the first place), I have no idea if there's a more efficient way or not.

    Given that the code is so short though, you might want to just rewrite the function from scratch. I've found that, if I recode something from scratch (assuming I fully understand what I'm recoding), it generally ends up working (and looking) better

Please reply to this thread with any new information or opinions.

Similar Threads

  1. looking for help access/excel
    By cusefan75 in forum Access
    Replies: 1
    Last Post: 07-22-2010, 09:06 AM
  2. Access without Excel
    By Matthieu in forum Import/Export Data
    Replies: 5
    Last Post: 11-10-2009, 12:32 PM
  3. Replies: 1
    Last Post: 08-31-2009, 10:24 AM
  4. Excel to Access
    By college_fellow in forum Programming
    Replies: 0
    Last Post: 12-18-2006, 08:01 PM
  5. Excel to Access
    By iturnrocks in forum Access
    Replies: 0
    Last Post: 08-02-2006, 10:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums