Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    Troy,

    If you just need the function in a particular workbook, then you can just place it in a general module in that workbook. If you require that it be available for all workbooks, then it is best placed in a general module of an AddIn (filetype xla). You could include all your user defined functions in a single workbook and save it as an add-in. eg. Myfunctions.xla. You can set a reference to this AddIn through Tools, AddIns and browsing to the location where you saved it.

    Andrew C

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    This Post will explain one way that Excel provides to do this.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding a custom function (VBA Excel 2000)

    <P ID="edit" class=small>(Edited by gwhitfield on 05-Jan-03 12:01. Hyperlink added)</P>Basic question. I very new to Excel VBA having done most of my work in Word. I found a custom function I want to used called LASTINCOLUMN.

    More info and code can be found at:

    http://j-walk.com/ss/excel/tips/tip30.htm

    How and where do I add this code so that I can use it as a function (i.e so I can put "=LASTINCOLUMN(B)" in a cell to calcalculate the last value in a column)?

    Thanks!!
    Troy

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    <P ID="edit" class=small>(Edited by TroyWells on 05-Jan-03 13:50. )</P>The problem was where I was putting this. I had tried putting this in code in the ThisWorkbook object, but couldn't access it. By putting it in a general module, as you mentioned below, I can now access it just fine.

    EDIT:
    One followup. I tried the other approach of saving it in a general module in an .xla file and setting a reference. However, it could not find the name of the function. It worked fine when I put it in a general module of a workbook. Why might this not work in the AddIn?

    Thanks!!
    Troy

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    Troy,

    It should work provided that the AddIn is loaded. Using an AddIn to hold userdefined functions isi the best approach, as you do not need to include the name of the container (workbook) when you invoke the the function. =MyFunction() should suffice.

    However perhaps I misled you by using the set reference terminolgy. Once you have saved your AddIn (select Microsost Excel Add-In(*.xla) in the Save as type dropdown) you can goto Tools (Excel menu, not VBA) , AddIns and if your AddIn is not included in the list of avialable AddIns use the browse button to locate it and then make sure the entry is ticked. Alternatively, you could save the AddIn in your XLStart folder, which would ensure it is loaded each time you start excel.

    Andrew C

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    Andrew,
    You did not mislead me. I did exactly as you said. I saved it in the addin folder and thus saw it in the list when I went to Tools > AddIns. I selected it, but the function was not available. It doesn't make sense to me why it was not available. I have it in a general module in the addin. It works fine if I put it in the worksheet, which meets my needs right now, but I'd sure like to figure out why it's not picking it up.

    I'm attaching the addin file in case you see something I'm missing.

    Thanks!!
    Troy
    Attached Files Attached Files

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    Troy,

    I loaded your add in and entered your function in a new workbook, and it worked just fine : returning the last entry in a given column.

    Are you getting an error message when you insert the function or is th efunction just not appearing in the function wizard list ?

    Also check th elist of VBA projects and see if th efile is included.

    Andrew

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    Sorry I should have put the error in.

    The cell displays:

    #NAME?

    I do see the addin in my VBA projects. I also can see it in the function wizard.

    Troy

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    Troy, This is becoming a puzzle.

    Have you tried inserting the function using th efunction wizard ?

    Andrew

  10. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    <P ID="edit" class=small>(Edited by TroyWells on 06-Jan-03 13:04. )</P>Yes I did, but I had the same results.

    Remember I told you that I put the macro in the .xls and it worked fine? I found that if I opened another xls while the xls containing the macro was open I could access that code as

    =Spreadsheet1.xls!lastincolumn(G1)

    and it works fine.

    I know the code is identical because I never altered it after pasting it from the website from which I swiped it. For that matter, it is working for you.

    I don't get it.

    EDIT:
    I tried this on a new spreadsheet and it works fine. I guess there is some problem with the spreadsheet I'm using. I've been using this spreadsheet for years without any other problems.

    EDIT2:
    I found the problem no longer exists if I copy all the worksheets from the existing xls to a new xls. It is possible that this spreadsheet started in Word 97 years ago. Perhaps Word 97 handled Addins differently. Thanks for your patience and help!!

    Troy

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding a custom function (VBA Excel 2000)

    If I'm not mistaken your function can be shortened to this:

    <pre>Public Function LASTINCOLUMN1(rngInput As Range)
    With rngInput
    LASTINCOLUMN1 = .Cells(.Cells.Count).End(xlUp).Value
    End With
    End Function
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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