As excel wont allow me to add hours more than 5 digit I use this public function to add two cells
Similarly if I were to select multiple cells alligned vertically then how do I create the function?
Please Login or Register to view this content.
I.e add more range?
Where's the rest of the function? How do you use it? What's in the cells?
Entia non sunt multiplicanda sine necessitate
What does this mean?Originally Posted by pannam
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
it means i can add hhhh:mm +hhhh:mm and not get an error but if i were to add hhhhh:mm+hhhh:mm Excel will give an error.
this is the code
i can add hhhhhhh:mm (cell A1) with hhhhhh:mm (cell A2) using =st(A1,A2)Please Login or Register to view this content.
but if i were to add multiple cells i.e A1:A9 I hit error. Hope you could guide me to properly phrase the function.
It might help if you could be more specific about the error you are getting. What is the error? What line/statement triggers the error?i can add hhhhhhh:mm (cell A1) with hhhhhh:mm (cell A2) using =st(A1,A2) but if i were to add multiple cells i.e A1:A9 I hit error.
If I may, is there an important reason for trying to make this an array function? I have posted a few times in this forum about how to make UDF's return multiple values, but I believe that it is easiest to code a UDF so it takes one set of input values and returns a single result. Without knowing your reasons for wanting to make this an array function, I would suggest that the easiest way to "properly phrase the function" is to leave it coded as is for a single result. Then, in the spreadsheet, when you need multiple results based on different inputs in a range, you will simply copy the function (make sure references in the function call are relative) and paste into the appropriate destination cells.
This should be no different than if you were using the built in SUM() function to add two cells together. Your first function call would be =sum(A2,C2). Then, to sum up each pair in A and C, you would simply copy this function down.
Why do you need a UDF at all?
C1: =A1+B1
This should give ON MS-off ver 2007,2010
C1: =A1+B1
I understand your logic sir. However You may want to take a look at the screen shot as I feel I may not have explained my issue properly.
Attachment 294373
Deleted. (double post)
Last edited by pannam; 02-02-2014 at 10:27 AM.
Sorry I'm late. I must have missed your last post.
As shg says, I don't see a need for a UDF to add times (assuming they are actual date/time values and not just text strings that look like time values). Assuming these are actual date/time serial values, the built in SUM() function should be able to add them up (or subtract them) just fine. However, assuming that you still want to pursue using this as an educational exercise for coding UDF's, here is my observation.
There is a fundamental difference between the function all =st(a1,b1) and =st(a1:e1) -- especially as it relates to the function as coded. As coded, the function requires two range arguments. The first function call [=st(a1,b1)] has two arguments and so the function can execute (as long as there are not other errors). The latter function call [=st(a1:e1) assuming that is what you are trying to do] passes only one argument. So Excel/VBA throws an error because you have passed only one argument to a function that requires two arguments.
I almost never use them, but I think what you might be wanting here is a single parametric array argument.
Help file for Function statement that introduces the use of the paramarray keyword for designating parametric arrays http://msdn.microsoft.com/en-us/libr.../gg264233.aspx
Tutorial explaining how to use parametric arrays in UDF's http://www.tushar-mehta.com/publish_...ramArray.shtml
Another tutorial: http://www.cpearson.com/excel/Option...rocedures.aspx
This is also probably going to require some restructuring of the rest of the code to cope with the paramarray. As I said at the beginning, I almost never use parametric arrays, so I'm not very good at using them. But the examples/tutorials given should give you a good introduction to the concept.
got what i was looking for here
http://www.excel-easy.com/vba/range-object.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks