logo
down
shadow

#VALUE error when dealing with long string in UDF in VBA(excel)


#VALUE error when dealing with long string in UDF in VBA(excel)

By : SharkVC
Date : November 18 2020, 11:13 AM
With these it helps I believe you have run into one of the obscure limitations in the interactions between VBA and Excel.
One workaround would be to change the formula to return only a single element, and have the particular element as an argument in the UDF.
code :
Option Explicit
Function longString(Optional R As Long = 1, Optional C As Long = 1)
        Dim res(1 To 1, 1 To 2)
        res(1, 1) = "hellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhellohh\n"
        res(1, 2) = "world"
        longString = res(R, C)
End Function
=longString()      <-- returns the first element
=longString(1,1)   <-- returns the first element
=longString(1,2)   <-- returns the second element
=longString(ROWS($1:1), COLUMNS($A:A))  <--could be dragged down and right to return an array of the elements


Share : facebook icon twitter icon
Error #VALUE with long string in UDF in VBA for output in Excel

Error #VALUE with long string in UDF in VBA for output in Excel


By : Sam
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I use the below UDF to concatenate references to include the result in , Regarding @Rory's comments :
code :
Public Function ConcatSQL2(Plage As Range, Optional Doublon As Boolean = True)
Dim A(0 To 0) As String, _
    myFile As String, _
    Cel As Range
'ReDim A(0)

A(0) = "('"
For Each Cel In Plage.Cells
    If (Cel.Value <> "" And (InStr(1, A(0), Cel.Value) = 0 Or Doublon)) Then
        A(0) = A(0) & Cel.Value & "', '"
    End If
Next
A(0) = Left(A(0), Len(A(0)) - 3) & ")"

myFile = "Path\FileName.txt"
Open myFile For Output As #1
Write #1, A(0)
Close #1

ConcatSQL2 = A
End Function
The text string you entered is too long excel error

The text string you entered is too long excel error


By : Juvencio
Date : March 29 2020, 07:55 AM
this will help Excel is thrown by the leading number. A simple work-around is to use a single quote mark. In the target cell start by typing '. Then, while still in edit mode, paste in the rest immediately after the single quote. That makes sure that Excel treats it as a string.
If you skip this and just enter the original string into a cell formatted as text, it actually seems to work even though it gives the error message you mentioned. It seems that the error message pop-up is itself the error, but the cell value itself is the desired string. This seems to be a an outright bug in Excel.
Scala Iterable[(String, Long)] to SortedMap[String,Long] or TreeMap[String, Long] sorted in decreasing order of value

Scala Iterable[(String, Long)] to SortedMap[String,Long] or TreeMap[String, Long] sorted in decreasing order of value


By : user2623085
Date : March 29 2020, 07:55 AM
this one helps. You can't do that. SortedMap sorts by keys, not values. If you want it sorted by value, you gotta use ListMap, and can't avoid converting to List:
code :
 ListMap(i.toList.sortBy(-_._2):_*)
HADOOP PIG error related to ORDER dealing with int long

HADOOP PIG error related to ORDER dealing with int long


By : Ishwaryaa Balaji
Date : March 29 2020, 07:55 AM
What does this scala error mean : "type mismatch; found : (((Long, Long), Long),) required: (((Long, Long), Long))

What does this scala error mean : "type mismatch; found : (((Long, Long), Long),) required: (((Long, Long), Long))


By : Влад Ковалёв
Date : March 29 2020, 07:55 AM
this will help You created outputTuples just fine, but you didn't create a function from output tuples to a string.
It says it wants type (((Long, Long), Long)) => String, which means it's a function taking ((Long, Long), Long) as an argument and producing a string. Here is one such function:
Related Posts Related Posts :
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org