none
Applying custom functions with different arguments to a column in Excel

    Question

  • Hi, 

    i am new to VBA programing so of course i am having some problems.

    Anyway, i have a table in excel which is imported from SQL server. this table will be growing all the time. Withing this table there are numerous columns with some raw values of some sensors. These values needs to be conveted (recalculated) with several argument. Each column represents different sensors and usses different arguments to convert the values. I have a seperate Sheet (Calib) which carries this calibration values.

    I created a UDF function which worked ok, until i re-import the data from SQL server (that the references get lost). 

    I also tried with this code : 

    Sub Gumb1_Klikni() Dim i As Range Dim j As Range Dim k As Range For Each i In Range("B2:b30") Dim Cf As Double Dim Lu As Double Dim Ct As Double Dim T0 As Double Dim B0 As Double Lu0 = Sheets("Calib").Range("B4") Cf = Sheets("Calib").Range("B2") Ct = Sheets("Calib").Range("B3") T0 = Sheets("Calib").Range("B5") B0 = Sheets("Calib").Range("B6") For Each j In Range("C2:C30") For Each k In Range("D2:D30") i = (Cf * (i.Value - Lu0) - Ct * (j.Value - T0) - (k.Value - B0)) * 0.1019665 + 159 Next Next Next End Sub

    ...but it is very slow...

    I guess my question is : Is there a way to apply UDF function with different arguments values to a specific columns?

    hope i explained ok...

    m

    Sunday, June 24, 2012 8:39 PM

All replies

  • Your code loops through B2:B30, C2:C30 and D2:D30 independently of each other, so the code in the inner loop is executed 29 * 29 * 29 = 24389 times. Is that really what you intend?

    Regards, Hans Vogelaar

    Sunday, June 24, 2012 8:46 PM
  • Hi, 

    thanks for reply.  

    I guess not. The problem is that i dont know how to do it other wise. There are several arguments that needs to be passed in the function. Six of them are constants (in Calib Sheet) which could be set as Range(), the other three are in three different columns in same table(Sava, tSava and Baro for Sava Column, PWS1, tPWS1 and Baro for PWS1 column etc...).  

    The goal is to  recompute Sava and PWS coulmns according to the equation in the VB Editor. But i don't know how to set these three different columns as variables (or arguments) in Function.

    Hope this explains it better, m

    Sunday, June 24, 2012 9:53 PM
  • I'm afraid that doesn't make it clearer for me, but perhaps you can use the following as starting point. It computes the values row by row, so it loops only once.

    Sub Gumb1_Klikni()
        Dim r As Long
        Dim m As Long
        Dim Cf As Double
        Dim Lu0 As Double
        Dim Ct As Double
        Dim T0 As Double
        Dim B0 As Double
    
        Lu0 = Sheets("Calib").Range("B4")
        Cf = Sheets("Calib").Range("B2")
        Ct = Sheets("Calib").Range("B3")
        T0 = Sheets("Calib").Range("B5")
        B0 = Sheets("Calib").Range("B6")
    
        m = Range("B" & Rows.Count).End(xlUp).Row ' or use m = 30
        For r = 2 To m
            Range("B" & r).Value = (Cf * (Range("B" & r).Value - Lu0) - _
                Ct * (Range("C" & r).Value.Value - T0) - _
                (Range("D" & r).Value - B0)) * 0.1019665 + 159
        Next r
    End Sub


    Regards, Hans Vogelaar

    Sunday, June 24, 2012 10:04 PM
  • Hi again, 

    i tried your code but i get 424 error : Object requiered on Range("B" & r).Value....

    I'll try exaplining it again. 

    So as you could probably se i have several columns in the table which needs to be recomputed i order to get  "real" values. The equation on whihch the recalc should got through is :

    a = (Cf * (Lu - Lu0) - Ct * (T-T0) - (B - B0)) * 0.1019665 + Z

    PArameters Cf, Ct, Lu0, T0 B0 and Z are calibration (constants) data which is stored in a Sheet called Calib. These data is different for each sensor (i have  13  sensors). 
    Parameters Lu, T, B are (variables) stored in three different columns in a table (called Data). So for eacg calculations of a i have three columns in table Data and 6 cells in table called Calib.

    Hope this makes it clearer, m

    Monday, June 25, 2012 8:09 AM
  • The error message would seem to indicate that there is no active sheet when you run the code.

    Regards, Hans Vogelaar

    Monday, June 25, 2012 9:31 AM
  • Even if i write it like this :

    Sub Gumb1_Klikni()
    
        Dim wb As Workbook
        Dim ws As Worksheet
        
        Set wb = ActiveWorkbook
        Set ws = Sheets("data")
        
        Dim r As Long
        Dim m As Long
        Dim Cf As Double
        Dim Lu0 As Double
        Dim Ct As Double
        Dim T0 As Double
        Dim B0 As Double
    
        Lu0 = Sheets("Calib").Range("B4")
        Cf = Sheets("Calib").Range("B2")
        Ct = Sheets("Calib").Range("B3")
        T0 = Sheets("Calib").Range("B5")
        B0 = Sheets("Calib").Range("B6")
    
        m = Range("B" & Rows.Count).End(xlUp).Row ' or use m = 30
        
        For r = 2 To m
            ws.Range("B" & r).Value = (Cf * (ws.Range("B" & r).Value - Lu0) - _
                Ct * (ws.Range("C" & r).Value.Value - T0) - _
                (ws.Range("D" & r).Value - B0)) * 0.1019665 + 159
        Next r
    End Sub

    I still get the same error....Object Required...

    thanks for the help.

    Monday, June 25, 2012 9:44 AM
  • I apologize - stupid error on my side. While copying some code, I erroneously duplicated the keyword Value in ws.Range("C" & r).Value.Value. This should have been ws.Range("C" & r).Value

            ws.Range("B" & r).Value = (Cf * (ws.Range("B" & r).Value - Lu0) - _
                Ct * (ws.Range("C" & r).Value - T0) - _
                (ws.Range("D" & r).Value - B0)) * 0.1019665 + 159
    

    Sorry for the confusion!

    Regards, Hans Vogelaar

    Monday, June 25, 2012 10:34 AM
  • Hi, 

    thanks, in the mean time i managaed todo this with muldimensional arrays, like this :

    Sub Gumb1_Klikni()
       'definiraj tabelo s kalibracijskimi podatki
       Sheets("Calib").Select
       myRows = 7
       myCols = 3
       
       Dim i As Integer
       Dim j As Integer
    
       ReDim calib(2 To myRows, 2 To myCols) As Double
       For i = 2 To myRows
        For j = 2 To myCols
            calib(i, j) = Cells(i, j)
        Next j
        Next i
        'MsgBox calib(2, 2) ' za testirat Calib inpute v arrayu
    
        'tabela s kalkulacijskimi podatki
        Sheets("Data").Select
        myRow = Range("B" & Rows.Count).End(xlUp).Row ' lahko myRow = 30
        myCol = 6 ' število stolpcev
        
        Dim l As Integer
        Dim z As Integer
            
        ReDim Data(2 To myRow, 2 To myCol) As Double
        For l = 2 To myRow
            For z = 2 To myCol
                Data(l, z) = Cells(l, z)
            Next z
        Next l
        'MsgBox Data(2, 2) ' zatestirat Data inpute
        'calculate
        'imam array Calib in array Data v arrayu
        
        'izracun Sava
        Dim a As Integer
        ReDim Sava(a To myRow) As Double
        ReDim pws1(a To myRow) As Double
    
        For a = 2 To myRow
            Sava(a) = (calib(2, 2) * (Data(a, 2) - calib(4, 2)) - calib(3, 2) * (Data(a, 3) - calib(5, 2)) - Data(a, 6) + calib(6, 2)) * 0.1019716 + calib(7, 2)
            pws1(a) = (calib(2, 3) * (Data(a, 4) - calib(4, 3)) - calib(3, 3) * (Data(a, 5) - calib(5, 3)) - Data(a, 6) + calib(6, 3)) * 0.1019716 + calib(7, 3)
        'MsgBox Sava(a)
        Next a
        Sheets("Result").Select
        Range(Cells(1, 1), Cells(myRow, 1)).Value = Application.WorksheetFunction.Transpose(Sava)
        Range(Cells(1, 2), Cells(myRow, 2)).Value = Application.WorksheetFunction.Transpose(pws1)
     
           
    End Sub
    
    
    Thanks for all the help,m
    Monday, June 25, 2012 7:58 PM