Excel Vba function, is there any limit for no of characters?
I am using a function in excel VBA, I am extracting data from a long string of cell.
I am using this function
Function name(cell As Range) As Double Dim Textstrng As String Textstrng = cell.text name = InStr(1, Textstrng, "UpgradeApprovalCode=") End Function
When UpgradeApprovalCode= is within around 8000 characters in the string its showing result, like 7000. But when its far away like near first 10000 characters, its not showing #VALUE!, not result.
So is there any search characters limit or how i can overcome this. I am using office 2007.
Try using the Valueproperty rather than Text
Textstrng = cell.Value
Sub main() Dim cell As Range Dim sh As Worksheet Set sh = ActiveSheet Set cell = sh.Cells(1, 1) Call GetName(cell) End Sub Function GetName(cell As Range) As Double Dim Textstrng As String Textstrng = cell.Value GetName = InStr(1, Textstrng, "UpgradeApprovalCode=", vbTextCompare) End Function
Each cell has a Value, Value2, Text, and Formula value. Value and Value2 can get up to 32K, but Text is limited to a length of 8221. Formula length can be longer, but when it gets over 8192 characters, it can only be saved in XLSB format. I'd hate to see that formula!
The limit for display in a cell in 32k characters (not all will be visible, but they're there in the cell).
As far as VBA is concerned, the string can be 2GB long (32-bit signed long number of elements). So the issue is certainly not a VBA limitation.
Hence D.Brett's answer of .Value shows what's actually in the cell rather than just the text.