Asked by:
Applying custom functions with different arguments to a column in Excel

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 reimport 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
Question
All replies


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

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

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 * (TT0)  (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


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.

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

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