+ Reply to Thread
Results 1 to 20 of 20

VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

  1. #1
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    I hope I don't need to give much description about the issue since the title and the code with an example file describes the issue very well.

    Please Login or Register  to view this content.
    Any suggestion how to overcome this issue?
    Attached Files Attached Files


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  2. #2
    Registered User
    Join Date
    02-24-2016
    Location
    The Netherlands
    MS-Off Ver
    2010
    Posts
    91

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Hi,

    On Microsoft site is stated:
    Returns a Characters object that represents a range of characters within the object text. You can use the Characters object to format characters within a text string.
    There is also an example:
    Please Login or Register  to view this content.
    See for yourself at https://msdn.microsoft.com/en-us/lib.../ff198232.aspx

    For what you want, you can try this:
    Please Login or Register  to view this content.
    WouterM
    The Netherlands

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Quote Originally Posted by WouterM View Post
    For what you want, you can try this:
    Please Login or Register  to view this content.
    Using Left(),Right(), VBA.Replace(),w.Replace() and w.Substitute will remove the actual formatting of the Text. That's why I am very keen on using VBA Characters() function.

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,783

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    @WouterM

    I came across that article aswell, but no where is it written that there is a limit of 260 Characters or so..
    Cheers!
    Deep Dave

    www.NeedForExcel.com

  5. #5
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,698

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    The notorious SendKeys could be handy sometimes :
    Please Login or Register  to view this content.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Quote Originally Posted by karedog View Post
    The notorious SendKeys could be handy sometimes :
    Thanks for your reply, I request you to go through the Post #3 content to know the actual need of VBA Characters() function.

  7. #7
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,698

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    You are welcome, I request you try the code first, and see if that is qualified to solve the problem that can't be done by code on your first post.

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Quote Originally Posted by karedog View Post
    You are welcome, I request you try the code first, and see if that is qualified to solve the problem that can't be done by code on your first post.
    Still you are missing my point. If you see in my attached example file in a1 cell the word "abc" is repeated in several times from that I have to identify the Bold abc part and delete the rest of the data by keeping all the words formatting as it is. Some of the abc's in different colors I want to keep all the formatting's of each words (bold,italic etc., of each words) which can be handled by VBA Characters() function alone (As far as I know).

    Hope I made it clear.

  9. #9
    Registered User
    Join Date
    02-24-2016
    Location
    The Netherlands
    MS-Off Ver
    2010
    Posts
    91

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    In the old days there was a limit of 255 character
    Nowadays the limit is 10,240 characters.

    see: https://support.microsoft.com/en-us/kb/105416

    I think the characters property of range has also this limit of 255 characters.

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Quote Originally Posted by WouterM View Post
    I think the characters property of range has also this limit of 255 characters.
    Yes I agree but it's working upto 261 characters not matching with exact 255 characters condition too..

  11. #11
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,783

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Quote Originally Posted by WouterM View Post
    In the old days there was a limit of 255 character
    Nowadays the limit is 10,240 characters.
    Exactly.. But the 255 Characters limit is pretty old.. And Sixthsense is facing the issue after the 260th character..

  12. #12
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,698

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Word's COM object has range object that acts similiar to Excel's characters object, for example :
    - Word's Range(param1,param2) is like Excel's Range.Characters(param1,param2)
    - Word's Range.Bold is like Excel's Range.Characters().Font.Bold
    - etc


    And now :
    Quote Originally Posted by :) Sixthsense :) View Post
    .... I have to identify the Bold abc part and delete the rest of the data
    This sample code should do :
    Please Login or Register  to view this content.

    Should this is still not what you want (so I'm still missing your point), you can modify it by yourself, the point that I want to make is, Word's range object can overcome the length limitation problem of Excel's characters object (as this case).

    This is my last post on this thread, have a nice day, and good luck.

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Really thankful for your wonderful suggestion which really helps me in overcoming the excels character() function issue

    Do you have any idea what I am doing wrong in the below code?

    I would like to replace the bold abc part with "def" and after that I would like to copy paste the total content (Replaced Content) back to the same excel cell. At present it's pasting only the search text in excel. I am confused about what I am doing wrong

    Please Login or Register  to view this content.
    ReplaceAll Source:https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx

  14. #14
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    After doing several testing the word object is also failing when it is called from excel.

    Replacement happening nicely when the macro runs in word.

    Please Login or Register  to view this content.
    But the below code fails when it is called from excel and it's not doing the replacement task as expected and resulting the same input value as output.

    Please Login or Register  to view this content.
    Attached sample files with code for easy testing.
    Attached Files Attached Files
    Last edited by :) Sixthsense :); 03-02-2016 at 05:01 AM. Reason: Included Attachments For Easy Testing

  15. #15
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    If you do late bind you must supply value for wdReplaceAll constant.
    • 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

  16. #16
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Put this in module and run !!


    Please Login or Register  to view this content.
    Attached Files Attached Files

  17. #17
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    I received this below Ping Message from karedog after 3 hours from posting the Post #14 content

    ------------------------------------------------------------------------------------------------
    Well, I am not a person that can take back his own words easily, and I've said that I won't post anymore in the thread, so here it is, using a Private Message.


    1. The reason your code in post #13 failed is :

    Please Login or Register  to view this content.
    the "wdReplaceAll" constant is private to Word's environment, it is unknown to Excel's, so either substitute this directly with a number, or define it using const keyword like this :

    Please Login or Register  to view this content.

    2. The reason that you have to do manouver using tables object on post #14, is because the oRng is set as a whole document content, including extra carriage return (enter) :
    Please Login or Register  to view this content.
    So if this oRng is to be pasted back to Excel without resizing, the extra carriage return is also included.
    The easy way to overcome this is by using selection object, first select first paragraph only, then copy this selection into clipboard, and then paste in Excel :
    Please Login or Register  to view this content.

    The complete code :
    Please Login or Register  to view this content.

    Hope this help.


    Regards
    ------------------------------------------------------------------------------------------------

    I would like to thank him for his extended supported via ping message too..

  18. #18
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Quote Originally Posted by Izandol View Post
    If you do late bind you must supply value for wdReplaceAll constant.
    Yes it worked when it is made as

    Please Login or Register  to view this content.
    Thanks for fixing that issue

  19. #19
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    Quote Originally Posted by Mandeep Baluja View Post
    [CODE]Range("B" & i).Value = Mid(Range("A" & i), Start, End1 - Start + 1)
    Thanks for your suggestion, but please refer post #3 content.

  20. #20
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,667

    Re: VBA Characters() Function Fails when the Cell Content Exceeds 261 Characters

    I am facing issue in activesheet.paste line and the detailed code is given in the below link.

    http://stackoverflow.com/questions/3...s-failed-error

    Anyone knows which causes this error?

+ 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] code exceeds max. # of characters per line
    By adamheon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-19-2014, 11:35 AM
  2. Merge cell content and remove duplicate characters
    By Jens D in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-19-2011, 11:40 AM
  3. Replies: 1
    Last Post: 08-17-2011, 05:59 AM
  4. importing data that exceeds 255 characters
    By dmac_59 in forum Excel General
    Replies: 0
    Last Post: 10-31-2007, 06:22 PM
  5. Calculating a formula that exceeds 1,000 characters
    By w1nter11 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2006, 09:25 AM
  6. Delete A Word That Exceeds X Number Of Characters
    By brazen234 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-31-2005, 12:40 PM
  7. Cell Content Greater than 24 Characters.
    By dlnorris in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2005, 04:18 PM
  8. [SOLVED] Footers in which number of characters exceeds 255
    By DRK in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-09-2005, 01:05 PM

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