cell declaration with function of vba

[rasid]

Hello,

I have a Problem with the programming the vba function. It looks like that:

Function Name (Frequency [Cell A1 to A10], Answer [Cell B1 to B10])

The workflow for the program is available, but I have problems to bring the Cells from A1 to A10, also B1 to B10, to the line with the function. How can I program that?
At the end I will give the command on a Cell =myFunction and read the needed Cells.
Regards

[vba_php]

are you asking about how to put ranges of cells into arguments of functions?

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[rasid]

Hello vba_php.
Yes, that is exact what I am looking for. How can I build it, that I can afterwards use it in excel when I want to give the cell values as arguments to the vba function section. Please, you have an idea?

[vba_php]

check this out:

https://www.google.com/search?q=exce...tion+arguments

you can put almost anything in arguments. but not everything. it seems as though this IS possible. let us know if you can't get what you need from that link. it's a great learning tool.

[MrShorty]

I wrote this tutorial a while ago that covers my strategies for building UDFs -- including some about using arguments in UDFs. See if this helps with your question: https://www.excelforum.com/tips-and-...uild-udfs.html

[Norie]

All you should need is something like this.

Function Name (rngFreq As Range, rngAns As Range)
   ' /// DO STUFF WiTH rngFreq and rngAns ///
End Function

Which can be called from a worksheet cell like this.

=Name(A1:A10, B1:B10)

[rasid]

Oh, many thanks. I will discover all and if I would have any question, I will come back to you "all"

[rasid]

Hello Norie,
short question. For example, I want to compile like this c=LOG10(CELL(A1)/CELL(A2)).......
How can I do this without a possibility to enter the RANGE object or the value? I must operate the values of the cells as given points. I will show you my problem in a picture. Thanks

[Norie]

Where do A1:A10 and B1:B10 come into this?

Also, I thought you had a 'workflow' for the program?

[rasid]

I thought i can transfer the Cell-Values (A1:A11 and B1:B11) on the begining (after RMS). I thought I can use the cells directly from the excel sheet like you build a curve. The work with it will happen between Function and end function. I think this is easy and I can deal with it. But my problem is to give the cell-values (in this example:
Frequency Answer
10 1 <-these are two cells in excel
15 2 <-these are two cells in excel
20 5 <-these are two cells in excel

Where can I transfer this Cell-Values (10,15,20,1,2,5 and many else) from excel to the function?

c=LOG10(2/1)/LOG10(15/10) -> this is only an example. I will work further with the other elements of the program you can see on the picture (Area and so on). This is easy! But how can I transfer/deliver the 10,15,20,1,2,5 to this function section?

For example: SUM(CELL1:CELLXX)

Regards

[Norie]

Assuming the values are in cells you can transfer them as I demonstrated in post #7.

You can then refer to the actual values in the code in various ways.

For example if you wanted to get a sum of the cells passed via rngFreq you could use this.

sumFreq = Application.Sum(rngFreq)

If you wanted to loop through the values in A1:A10, and B1:B10 to calculate c you could try this.

For idx = 1 To rngFreq.Rows.Count-1
    c = LOG10(rngName.Cells(1, idx+1)/rngName.Cells(1, idx))/LOG10(rngFreq.Cells(1, idx+1)/rngFreq.Cells(1, idx))
Next idx

P.S. I would suggest you use more appropriate names in the code, do you really want the function to be called Name and the 2nd argument to be named rngName?

You might also want to introduce variables for x₁, x₂, y₁ and y₂.

[rasid]

Thank you!

[rasid]

Hello Norie,

I did everything like I thought it is right. Please see the pictures. But I got the error (I think) #WERT
What does it mean and where I did a mistake? You can see, I wrote for the CELL =RMS(A1:A9,B1:B9)
Thanks

[Norie]

Rather than images can you upload a sample workbook?

[rasid]

Sorry, what do you mean with sample workbook? I think the major problem is, how to give the result back to the cell.

[MrShorty]

As I explained in my tutorial (link in post #6), one of the first things I do when building a UDF is add a Stop statement to it (usually very early in the procedure) so I can enter debug mode use VBA's debugging tools to understand what is happening.

If I follow the code in the picture correctly, your .cells(1,i) structure suggests that you are expecting to pass a horizontal range to each argument, but your picture of the spreadsheet shows vertical ranges. If I were to debug this, I would add the Stop statement as indicated, then follow what is happening in each loop and see what VBA is doing with a vertical range in code that seems to be expecting a horizontal range. My guess is that you will find that you getting "trying to take the Log of 0" or "divide by 0" errors as VBA encounters empty cells (which are treated as 0).

Of course, fixing that problem, if that proves to be the problem, is about structuring the procedure so it can expects vertical ranges instead of horizontal (if you know you will always pass vertical ranges to it) or so that it can handle either vertical or horizontal ranges (if you want the procedure to have that flexibility). Maybe include some error trapping if someone passes an invalid range to the function.

[Norie]

I could try and reproduce things by peering at the images to copy the code and values but it would be far easier if you could upload a workbook with sample data and code.

If you can't do that you could post the code so we can easily copy it.

[rasid]

Oh, yes....I am sorry.

Function Rms(rngFreq As Range, rngAnt As Range)
  Dim c As Double
  Dim area As Double
  c = 0
  area = 0
  Dim i As Long
  For i = 1 To rngFreq.Rows.Count - 1
    c = Log(rngAnt.Cells(1, i + 1) / rngAnt.Cells(1, i)) / Log(rngFreq.Cells(1, i + 1) / rngFreq.Cells(1, i))
    If c > 0 Then
      area = rngAnt.Cells(1, i + 1) / (c + 1) * (rngFreq.Cells(1, i + 1) - rngFreq.Cells(1, i) * (rngFreq.Cells(1, i) / rngFreq.Cells(1, i + 1)) ^ c) + area
    ElseIf c < 0 Then
      If c = -1 Then
        area = rngAnt.Cells(1, i) * rngFreq.Cells(1, i) * Log(rngFreq.Cells(1, i + 1) / rngFreq.Cells(1, i)) + area
      Else
        area = rngAnt.Cells(1, i) / (c + 1) * (rngFreq.Cells(1, i + 1) * (rngFreq.Cells(1, i + 1) / rngFreq.Cells(1, i)) ^ c - rngFreq.Cells(1, i)) + area
      End If
    Else
      area = rngAnt.Cells(1, i + 1) * (rngFreq.Cells(1, i + 1) - rngFreq.Cells(1, i)) + area
  End If
  Next i
  Rms = Sqr(area)
End Function

5	0.077488745
21.625	0.077488745
29.125	0.830968614
42.5	0.830968614
61	0.0508
175	0.0508
304	0.0025
365	0.001454756
2000	0.001454756

I hope this helps

[Norie]

MrShorty is right, the row/column are the wrong way round which is my fault really as I that's how I had them in post #12.

Here's the code with the row/column the right way round.

Function RMS(rngFreq As Range, rngAnt As Range)
Dim c As Double
Dim area As Double
Dim i As Long

    For i = 1 To rngFreq.Rows.Count - 1
        c = Log(rngAnt.Cells(i + 1, 1) / rngAnt.Cells(i, 1)) / Log(rngFreq.Cells(i + 1, 1) / rngFreq.Cells(i, 1))
        If c > 0 Then
            area = rngAnt.Cells(i + 1, 1) / (c + 1) * (rngFreq.Cells(i + 1, 1) - rngFreq.Cells(i, 1) * (rngFreq.Cells(i, 1) / rngFreq.Cells(i + 1, 1)) ^ c) + area
        ElseIf c < 0 Then
            If c = -1 Then
                area = rngAnt.Cells(i, 1) * rngFreq.Cells(i, 1) * Log(rngFreq.Cells(i + 1, 1) / rngFreq.Cells(i, 1)) + area
            Else
                area = rngAnt.Cells(i, 1) / (c + 1) * (rngFreq.Cells(i + 1, 1) * (rngFreq.Cells(i + 1, 1) / rngFreq.Cells(i, 1)) ^ c - rngFreq.Cells(i, 1)) + area
            End If
        Else
            area = rngAnt.Cells(i + 1, 1) * (rngFreq.Cells(i + 1, 1) - rngFreq.Cells(i, 1)) + area
        End If
    Next i
    
    RMS = Sqr(area)
  
End Function

[rasid]

Thank you and MrShorty very much. I will try this. Regards

[Norie]

rasid

Did you try the code I posted? (post #20)

[rasid]

Hello Norie,

sorry for the delay. Thank you it worked.
Nice Weekend!

[Norie]

It worked for me, see the attached workbook.

[rasid]

Sorry. I made am mistake and edit my post. Please ignore the first
Thank you!