+ Reply to Thread
Results 1 to 19 of 19

Excel VBA function to add values from two columns

  1. #1
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Excel VBA function to add values from two columns

    Hey guys, I'm new to this, hope u can help me.
    I want to define a function in excel using vba function to add values in two columns and put the result values in a third column, like as this:

    Capture.PNG

    I wrote this code but does not work:
    Please Login or Register  to view this content.
    I will appreciate any help
    Last edited by abbsha; 06-22-2022 at 11:19 AM.

  2. #2
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Excel VBA function to add values from two columns

    I think you've misunderstood something along the way.

    A Function is a way to create a new Formula you can use in Excel, obviously you've got the SUM formula already and so don't need to create a new Formula to do this.

    From what I can tell from your code you want to automatically add two columns together in a script, if that is the case then you need something more like this;

    Please Login or Register  to view this content.
    Does that help?

  3. #3
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Re: Excel VBA function to add values from two columns

    Thanks for your answer, actually I know that this formula already exist but I want to know how to write that in VBA, could you please write your above code for VBA Function please?

  4. #4
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Excel VBA function to add values from two columns

    It might be me that has misunderstood then.

    As far as I know you can't create a Function to go through a range automatically in the way your code seems to be trying to do to output an answer like that.

  5. #5
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Re: Excel VBA function to add values from two columns

    Thanks again, ok so could we have your code for not a specified range (in your code it restricted to col. A and B)? I mean user could select any column.

  6. #6
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Excel VBA function to add values from two columns

    Sure, but I think we should be clear on what it is you want as this could mean different things and different approaches.

    What is it you want to happen exactly? - How do you see an End User using this?

    (I might not be able to get back to this until tomorrow now - I'm sure someone else might be able to help between now and then, I'll check back in tomorrow though to see.)

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Excel VBA function to add values from two columns

    Do you actually mean a function as an excel function or a function as in "to get something done".
    If the latter, a regular macro should do the trick.
    Please Login or Register  to view this content.
    Last edited by jolivanes; 06-20-2022 at 12:51 PM. Reason: spelling
    Experience trumps academics every day of the week and twice on Sunday.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Excel VBA function to add values from two columns

    I'm going to echo PaulSP8's question of exactly what do you want here. If I'm understanding correctly, you want a UDF that:
    A) Takes 2 ranges (as vectors/arrays) as input. (Appears you already know how to do this).
    B) Sums corresponding elements of these two ranges/vectors/arrays and stores these results in a new vector/array. You clearly know how to loop through and sum them, but you are not storing the results in an array.
    C) Output the array/vector of results to the spreadsheet. This seems to be the main thing missing, if I'm understanding correctly. I would note that we don't usually have a UDF return an array unless there is some compelling reason to return an array (in the example given, I don't see a compelling reason to return an array). Recognizing that sometimes, the examples given are just placeholders for the real task, perhaps your real task does compel you to return an array. If it helps, this is the programming strategy I use for a UDF that needs to return an array: https://www.excelforum.com/excel-pro...ml#post4071488

    Assuming I understand, it looks like all that is missing from your UDF is to dimension an array to hold the results, then add the final assignment statement so that the UDF can return the array result to the calling cells.

    One thing to note -- a 1D VBA array will be returned to Excel as a horizontal array. You will need to choose whether or not to have the UDF return a horizontal array (my preference) then nest the function call inside of a TRANSPOSE() function, or dimension a 2D array/vector for the results array.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Re: Excel VBA function to add values from two columns

    Thanks guys, ok seems i must clear this more, I know we can do this easily by using “+” or “Subtotal” function in excel but I am trying to write a function by myself to calculate the summation and maybe I could even modify my function later, but at this stage I simply want to write a function to add two selected columns and put the result in a third column. I am familiar with MATLAB programming but am new to VBA and don’t know how to define array or input, output and etc. I can write this function to add two cell values like this:

    Public Function SumNew(a,b)
    SumNew= a+ b
    End Function

    But I cannot generalized it to ranges. I hope I explained good my problem, thanks again for your helps 🙏🏻😊

  10. #10
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Excel VBA function to add values from two columns

    OK - This will let you select any two coluimns and have the total of them added into the third column next to them.

    I've tried to think of things that can go wrong - This only works if there are two columns selected, and if there is numbers in the cells.

    Please Login or Register  to view this content.
    Is that what you wanted?

  11. #11
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Re: Excel VBA function to add values from two columns

    Thanks Paul, I appreciate your help, this is exactly what i want but is that possible to write this code as a Function not a Sub?

  12. #12
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Excel VBA function to add values from two columns

    I can't help turn that into a Function, sorry abbsha - Functions aren't something I really work with and getting one to work like how you want is beyond me.

    @MrShorty seems to have some ideas in Post #8 so maybe he can help you further now we've gotten to the root of what you want.

    Sorry I can't finish the job for you?

    (I'd be interested to see if this could be done as a function)

  13. #13
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Excel VBA function to add values from two columns

    I am familiar with MATLAB programming but am new to VBA and don’t know how to define array or input, output and etc.
    Unless I am missing something, I would disagree. You clearly know how to do input by appropriately defining arguments in the Function statement and including those arguments in the function call. You are assigning a result to the function name, which is the appropriate way to get output to the spreadsheet, so you clearly know how to do output. While I am not familiar with MatLab, just about every programming language I am familiar with has some kind of array structure and syntax, so I expect you know something about arrays in a generic sense, so the only thing that might be lacking is VBA specific syntax for working with arrays (maybe start here: https://docs.microsoft.com/en-us/off...d/using-arrays ). It might help us help you if we understood exactly what part of this you are having trouble with.
    I can write this function to add two cell values like this:

    Public Function SumNew(a,b)
    SumNew= a+ b
    End Function
    This is what I was referring to when I talked about a compelling reason to have an array output. The example problem doesn't require array output. I would use this very simple UDF, enter it into the top cell, then copy/paste/fill down. You as the programmer need to understand your problem well enough to decide when your UDF needs array output and when it does not.

    If you decide that array output is indeed necessary (because the simple summation in your example is not an exact representation of the actual task you are trying to perform), then you need to do as I outlined in the other post -- dimension an output array to store the array of results, then assign that array to the function name. This might look like (using your code from the OP as a starting point):
    Please Login or Register  to view this content.
    Called from the spreadsheet as =TRANSPOSE(SumNew(A2:A11,B2:B11)) [using the TRANSPOSE() to convert the horizontal array to a vertical array].

  14. #14
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Re: Excel VBA function to add values from two columns

    Thanks Paul, I appreciate your kindness.
    Hey MrShorty, thanks a lot for your explanations, I think now we are close to what I want. Your last code inputs two arrays instead of two values but still output is the problem. I know we can extent the formula to other cells but I want the formula to do it automatically like as the Paul's code which automatically outputs the result in an array. Your last code only output the summation of the first values of two inputted arrays and not all selected values in the arrays. For your last code, for example is it possible to output 10 summation values instead of only one?

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Excel VBA function to add values from two columns

    It isn't outputting 10 values? I don't replicate that result. When I use the function, I get 10 values output. Places to look at for debugging:

    1) Add a Stop statement at the end (and maybe the beginning) so you can see the result array(s) before output.
    Please Login or Register  to view this content.
    When it enters debug mode, find the locals window and see what is in temparray and whether SumNew looks like an exact copy of temparray

    2) Your profile says you are using Excel 2019. Assuming the spill feature works with UDFs (I have long been led to believe that it does), the spill feature should automatically detect the array output and fill in the 10 values. If, for some reason, the spill feature is not working as expected, someone else will need to debug, as my older version does not spill.
    2b) If your profile is in error, and you are using this on an older version of Excel that does not have spill, make sure you are entering it as an array function.
    3) The only other expected error is that you are neglecting to include the TRANSPOSE() function.

    If those steps don't reveal the error, then we will probably need you to provide more detail about how you are implementing this. By all my accounts, it should output all 10 values correctly. If it is not, there is something missing.

  16. #16
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Re: Excel VBA function to add values from two columns

    Well MrShorty, thanks a lot, I selected 10 cells and pressed Ctrl+Shift+Enter and it worked 👍🏻😊, now is that possible to make two more change in your code:
    1- Modify it to work for unspecified range (now is only for 10 values)
    2- Modify to work for columns without Transpose function (now works for 10 selected cells in a row without Transpose function)

  17. #17
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Excel VBA function to add values from two columns

    Something like this:

    =SumNew(A1:A12,B1:B12)

    confirmed by Ctrl-Shift-Enter combination.

    HTML Code: 
    Attachment 784627
    Quang PT

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Excel VBA function to add values from two columns

    abbsha,

    Since you are with 2019, you should have Spill functionality.
    Enter formula like
    =A1:A10+B1:B10 in C1
    will give you what you want.

    If earlier version
    Select C1:C10, enter formula =A1:A10+B1:B10 and confirm with Ctrl + Shift + Enter (array formula entry)

    If UDF, work with all version of Excel
    Use in cell in C1
    =IFERROR(SumSpill($A$1:$A$5,$B$1:$B$5,ROW(A1)),"")
    then copy down
    Please Login or Register  to view this content.
    Last edited by jindon; 06-22-2022 at 03:26 AM.

  19. #19
    Registered User
    Join Date
    06-20-2022
    Location
    Iran
    MS-Off Ver
    2019
    Posts
    10

    Re: Excel VBA function to add values from two columns

    Thanks you bebo, that's what I wanted, Anyway, Thanks All for taking time to respond my question.
    This code works well:
    Please Login or Register  to view this content.
    Again thanks All for your helps, I appreciate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] What function could help look at values in different columns & subtract based on values?
    By AccountingJ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2018, 04:18 PM
  2. [SOLVED] Excel Function for checking values in 3 columns and returning a value
    By kian82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-28-2017, 07:57 AM
  3. If function to look at two columns and return values
    By Rucille in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-02-2014, 06:45 PM
  4. [SOLVED] Excel function or script for concatenate all values from B:B for same columns in A:A
    By Odeen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-27-2012, 10:29 AM
  5. [SOLVED] Function for VLookup using values for two columns
    By m.raby in forum Excel General
    Replies: 2
    Last Post: 07-30-2012, 09:47 AM
  6. function to match values in 3 columns
    By brichigo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-11-2012, 12:04 PM
  7. columns in excel to reflect array of values entered in previous two columns
    By netvasi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2011, 01:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1