From Excel VBA to Access VBA

[theracer06]

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.

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

[Rawb]

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.

[theracer06]

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?

[Rawb]

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