+ Reply to Thread
Results 1 to 7 of 7

Creating a VBA Function

Hybrid View

  1. 08-04-2013, 07:55 AM #1
    ColdDeath
    ColdDeath is offline
    Registered User
    Join Date
    08-03-2013
    Location
    Maasbree, Nederland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Creating a VBA Function

    Hi,

    I'm trying to write my own excel funtion with VBA here. Currently I'm trying to replace this code (it's dutch):
     =ALS(E4="";"";ALS(E4=M4;L4;ALS(E4=M5;L5;ALS(E4=M6;L6;ALS(E4=M7;L7;ALS(E4=M8;L8;ALS(E4=M9;L9;ALS(E4=M10;L10;ALS(E4=M11;L11;ALS(E4=M12;L12;ALS(E4=M13;L13;ALS(E4=M14;L14;ALS(E4=M15;L15;ALS(E4=M16;L16;ALS(E4=M17;L17;ALS(E4=M18;L18;ALS(E4=M19;L19;ALS(E4=M20;L20;ALS(E4=M21;L21;ALS(E4=M22;L22;ALS(E4=M23;L23;ALS(E4=M24;L24;ALS(E4=M25;L25;ALS(E4=M26;L26;ALS(E4=M27;L27;ALS(E4=M28;L28;ALS(E4=M29;L29;ALS(E4=M30;L30;ALS(E4=M31;L31;ALS(E4=M32;L32;ALS(E4=M33;L33;ALS(E4=M34;L34;ALS(E4=M35;L35;ALS(E4=M36;L36;""))))))))))))))))))))))))))))))))))
    It works, but I've stumbled upon an error... I really don't want to break this code in a million peaces to find out what the problem is...

    So, I decided to write a function that does exactly the same, but looks 10 times better. The only problem is, yesterday I found this site that did exactly what I was looking for, now I lost it.
    I do know a few things when it comes to programming, I just don't know anything when it comes to excel programming...

    So far I have:

    If Target.cell > 0 Then

If Target.cell = M4 Then
Target.Value = L4

If Target.cell = M5 Then
Target.Value = L5

[.............]

If Target.cell = M35 Then
Target.Value = L35

If Target.cell = M36 Then
Target.Value = L36
    So now it can do the same as the function I've written with the if function. Now I want to active it by typing:
    =Journaalpost(E4)

    Kind regards,
    ColdDeath
    Reply With QuoteRegister To Reply
  2. 08-04-2013, 08:06 AM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Creating a VBA Function

    Before jumping to VBA you should try replacing all the IFs in that formula with something like VLOOKUP or INDEX/MATCH.

    Perhaps something like this.
    Formula: copy to clipboard 
    =IFERROR(INDEX(L4:L36, MATCH(E4,M4:M36,0)), "")


    PS That's Scottish.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 08-04-2013, 08:36 AM #3
    ColdDeath
    ColdDeath is offline
    Registered User
    Join Date
    08-03-2013
    Location
    Maasbree, Nederland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Creating a VBA Function

    =ALS.FOUT( INDEX( $L$4:$L$36; VERGELIJKEN( E82;$M$4:$M$36;0 )); "")

    It works! Thank you!
    Last edited by ColdDeath; 08-04-2013 at 08:39 AM.
    Reply With QuoteRegister To Reply
  4. 08-04-2013, 08:30 AM #4
    ColdDeath
    ColdDeath is offline
    Registered User
    Join Date
    08-03-2013
    Location
    Maasbree, Nederland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Creating a VBA Function

    Don't know what I'm doing wrong. I've rewritten the code in Dutch:

    Dutch
=ALS.FOUT(INDEX(L4:L36;GELIJK(E4;M4:M36));"")

English
=IF.ERROR(INDEX(L4:L36;MATCH(E4;M4:M36)), "")
    Problem is, it doesn't check if target cell E4 is equal to one of the cells in M4:M36. It just writes down list L4:L36 in that order and ends with a list of zero's.

    What does index do?
    What does match do?

    IFERROR is the opposite of IF I recon?
    So let's say I write:

    IFERROR(0=0;1;2)
    IFERROR does the opposite of IF, so in this case IF would write 1 and IFERROR would write 2. Correct?
    Reply With QuoteRegister To Reply
  5. 08-04-2013, 08:32 AM #5
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Creating a VBA Function

    You are missing the 0 in the MATCH/GELIJK function, which is actually a mistake I made when originally writing the formula.
    Reply With QuoteRegister To Reply
  6. 08-04-2013, 08:43 AM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Creating a VBA Function

    IFERROR is not the opposite of IF.

    IFERROR is used to return a value if an expression evaluates to an error.

    =IFERROR(<expression>, <valueiferror>)

    Try this which is how it would have been done pre-IFERROR.

    Formula: copy to clipboard 

    
=IF(ISERROR(INDEX(L4:L36, MATCH(E4,$M$4:$M$36,0))), "", INDEX($L$4:$L$36, MATCH(E4,$M$4:$M$36,0)))


    I've also made the lookup ranges absolute which you'll need if you are going to copy this formula down from row 4.
    Reply With QuoteRegister To Reply
  7. 08-04-2013, 08:48 AM #7
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Creating a VBA Function

    @Colddeath

    For future use it's easier as you post an excel file on the forum.

    Then you don't have to translate your formula from Dutch into English.

    Forummembers can add the solution and post it, and after you downloaded it, you get the formula translated in Dutch in your file. (excel will do that for you).

    Then there is also no problem with a ; (semicolon) and a , (colon).

    your translation of als.fout should be iferror (instead of IF.Errror) => there is no dot in the english formula.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help on creating a if function or a nested if function????
    By noob_excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2012, 12:39 AM
  2. Creating a function to simplify a more complex function
    By mk74 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-29-2011, 08:43 AM
  3. Help creating a function
    By pixelchick in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-17-2009, 09:16 PM
  4. [SOLVED] Creating a Macros function but co9ntent of function only works in a sub plz help
    By Alexandre Brisebois (www.pointnetsolutions.com) in forum Excel General
    Replies: 6
    Last Post: 07-20-2005, 04:05 PM
  5. [SOLVED] Creating a Function
    By teresa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-06-2005, 10:06 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1