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.

Answers


Try using the Valueproperty rather than Text

ie

Textstrng = cell.Value

Edit:

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.


Need Your Help

How to use Gradle with stand-alone Android SDK?

android build gradle android-sdk-tools

I use a stand-alone Android SDK with no IDE. When I create a new project its root directory contains files such as build.xml and ant.properties, so I have to use Ant to compile my projects.

What is the urllib3 documentation telling me to do?

python ssl openstack urllib3 keystone

I am trying to get a Kilo OpenStack cloud deployed and I was getting this error: