+ Reply to Thread
Results 1 to 5 of 5

Custom INDEX function that can handle >255 characters

  1. #1
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Unhappy Custom INDEX function that can handle >255 characters

    Hi,

    I am trying to use the `Application.Index` function with an variant 2D Array which contains some elements having >255 characters of text. This results in Variant/Integer Type `Type Mismatch` error. I am also unable to use `Application.Transpose` because of hitting this >255 characters limit.

    Has anyone made any `Custom INDEX UDFunction` that can handle >255 characters of text to overcome this limit?

    e.g.
    Index Type MisMatch error.PNG


    The snippet code looks like this:

    Please Login or Register  to view this content.
    There is no way to attach a powerpoint pptx file, so sharing the powerpoint template file from google drive.
    Attached Files Attached Files
    Last edited by junoon; 12-18-2019 at 11:36 AM. Reason: attached sample.xlsm file. But unable to attach sample pptx file

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Custom INDEX function that can handle >255 characters

    I didn't check everything, but you could try to correct/modify it that way (comments in the code) - see txt file:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Custom INDEX function that can handle >255 characters

    Hi @porucha vevrku,

    Thanks for your reply.

    Interestingly, though strange enough, i found that as the `Arr` is defined as a Variant and pulls in Range data .e.g.
    Please Login or Register  to view this content.
    I think it internally uses an integer index and therefore maintains a 255 character limit.

    But now if i define the `Arr` as a String, e.g.

    Please Login or Register  to view this content.
    ... and then use INDEX or TRANSPOSE, they are able to handle the >255 characters limit.


    Please Login or Register  to view this content.
    Sample.xlsm and PPT Sample Template.pptx
    Last edited by junoon; 12-19-2019 at 03:31 AM.

  4. #4
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Custom INDEX function that can handle >255 characters

    BTW, shouldn't this code snippet be like:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Custom INDEX function that can handle >255 characters

    Quote Originally Posted by junoon View Post
    ... and then use INDEX ...
    Please Login or Register  to view this content.
    Why use an "intermediary"/"agent" to extract data from a given array/area variable when you can do it directly ?
    Please Login or Register  to view this content.
    That sometimes the code seems longer is to cause it not to be used ?
    It is certain that the internal code of the "Index" function itself is many times longer, and only its calling is short.



    Quote Originally Posted by junoon View Post
    BTW, shouldn't this code snippet be like:
    Everything that works should be as it is.
    It often happens that already an hour later we write the same thing completely differently.
    For example, now, I would write it like that:
    Please Login or Register  to view this content.
    regards

+ 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] Index/Match greater than 255 characters using LEFT function
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-14-2016, 01:24 PM
  2. How to handle duplicate data using index and match function
    By zackdy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2014, 02:09 AM
  3. How to handle duplicate data using index and match function
    By zackdy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2014, 11:11 PM
  4. How To Handle Corporate Custom VBA Code for Excel
    By BlueCollarCritic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2011, 01:26 PM
  5. Replies: 1
    Last Post: 07-06-2010, 06:06 PM
  6. How to handle non-english characters in VBA?
    By Zepher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-01-2009, 11:19 PM
  7. Emulate Index/Match combo function w/ VBA custom function
    By Spencer Hutton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2005, 01:06 PM

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