The Appearance of a Cell & helping me make a udf to not act upon appearance of cell (but to act upon a stored value of the function arguments)

[Spyros13]

I tried to make a macro udf to answer someones strange question on StackOv.

Maybe I took it the wrong way and he was just asking as a freshman or 'just asking', but I treated it seriously. Thought he was a sudent fro his project or a busiess man. Anyway. It really got me going, The question was amazing wonderful it seemed to me.

"Q:
Is there any way to add ellipsis(three dots) at the end of a data in excel if it exceeds the limit of the cell? [closed]"

"Can we put lengthier data in limited space in excel as it disturbs view of the data in empty cells by going over it?

We have an alternative to wrap the text but that increases width of the cell. Rest if the cell width is too small we could see ########.

What i want is:

Abc, Batman, Spiderman | America, India, Australia |

To be shown as:

Abc, Bat... | America, Ind... | "

That was his qustion.

@juDV made an interesting point: "Using a formula would mean altering the actual value losing what you had when you started of. You sure that's what you want? – JvdV Jul 21 at 21:02 "

heress my resonses:

• Good question, Not that I want to change your question, but perhaps doing something else like changing highlight,/fill cell colour? font colour? or adding a comment mark to such cells? would any of those be other equivalent ways of solving your need or problem and satisfying all the benefits you imagine the ... would bring? – Spyros Tzortzis Jul 21 at 21:43
• 
  Not neccissarily @Judv im thinking. Because couldn't say a udf be written to store the actual value of the original formula resulyt in memory (which it is anyway)/an array, so the truncated portions of the original result, could be replaced per cell if doing a =myreplace(cell,"...",original data result of the cell) , or realresultUDF(cell). or something like udfGoBack/FlipBack?. ... No, hang on UDF would be like =Shownvalue(the original formulas,1 or 0) with 1 or 0 denoting the flip switch between real actual result and the ... version. I think it could be done/made. – Spyros Tzortzis Jul 21 at 21:55

so I set about doing it - why not - I had free time and was in my favourtite place at the moment (St Pauls area london).

This is what I came up with :

Function valueshown5(cell6 As Variant, toggleswitch As Boolean) As Variant
If toggleswitch = False Then
  valueshown5 = cell6
   Else

    If Len(cell6) > Application.Caller.ColumnWidth Then

    valueshown5 = Replace(cell6, Right(cell6, Len(cell6) - Application.Caller.ColumnWidth - 3), "...")

     Else: valueshown5 = cell6 & " - ok" ' it seems daft as this could go over too, but Its just to test it.

    End If
  End If
End Function

Which works. Does a job - atleast proved point it can be done.

But to answer address the experts concerns & my own suspictions & doubts (re: cell loosing its value), I tried to (and succeeded , at least in the meaning of discovering ways to parse own formula and arguments, Application.Caller.Formula & Application.ThisCell.Formula , which I became very excited about) make a better formula. Got it - forced it - to work:


Public MyString1e As Variant
Public MyString1u As String
Public arg22 As Variant

 Function JustAppear33AG(cell6 As Variant, toggleswitch As Boolean) As Variant 'changed cell6 from variant to string ||| wth arg2 only 25/7/2020 JustAppear33AG - next one will have arg2 evaluated [arg2]=arg2 18:47 25/07/2020
'Dim MyString As String
MyString1e = [cell6]
MyString1u = cell6
Dim cell67 As Range
Dim formulacell6arg As Variant 'its as string??? idk
formulacell6arg = Application.Caller.formula 'Value2

'thiscell2 = ThisCell.formula
'foo = Evaluate("Formulatext(" & ThisCell & ")")

'zz = GetFormula(cell6)
'zz = GetFormula(ThisCell)
'ccc = chr(34) & ThisCell & chr(34)
'ccc4 = Range(ccc).Formula2
Dim arg3 As Variant


'cell7 = Trim(cell6)
    'I = InStr(func, "(") 'nope dont need this. dont care whats in it. I just want it all as written by user
    arg1 = Mid(cell6, 1, Len(cell6)) 'did not work the way id expected it to (it just mid's the evaluation of cell6)
    'arg1 = Mid(args, 1, Len(args) - 1) swear I dont need this
'' This worked ;;    arg1 = Evaluate("Text(" & cell6 & ")")
''   arg2 = Evaluate("FormulaText(" & ActiveCell.Address & ")")
'' arg2 = formulacell6arg '=MID(AO26,LEN("JustAppear33AG(")+1,LEN(AO26)-LEN("JustAppear33AG(")-3)
    arg22 = Mid(formulacell6arg, Len("JustAppear33AG(") + 2, Len(formulacell6arg) - Len("JustAppear33AG(") - 4) 'first arg/par wss +1 - now its +2;44 25/07/2020)
    arg3 = Evaluate([arg22]) 'Worksheets.Evaluate(Trim(arg22)) '==="="  /// '''' Eval(arg2) '
      

'If Len(MyString1e) > 1 Then

If toggleswitch = False Then
  JustAppear33AG = Evaluate([arg22]) 'wow look what I have discovered ! 21:45 25/07/2020 :) W. LL HH LL Hh LL

   Else
 
   If (Len(cell6)) = Application.Caller.ColumnWidth Then
   JustAppear33AG = Evaluate([arg22])
   Else
    If Len(cell6) - 3 > (Application.Caller.ColumnWidth * 1.6666) Then '25/07/020 21:48 - had it arg3 just a minute ago. trying to improve the column reactions
  
    JustAppear33AG = Replace(arg3, Right(arg3, Len(arg3) - Application.Caller.ColumnWidth - 3), "...")
'Len(cell6) & " - " & Application.Caller.ColumnWidth '

     Else: JustAppear33AG = arg3 & " - ok" ' it seems daft as this could go over too, but Its just to test it.
'Len(cell6) & " - " & Application.Caller.ColumnWidth
'ActiveCell &
End If
    End If
  End If

' Else: JustAppear33AG = ThisCell.Address & arg3 & "Error" ' this just doesnt let single digit characters to be evaluated/ I was trying something else but that didt work,so just chnged the parameters to 1-digit
  'End If
  
End Function
    'cound trey & chnge arg3's to cell6 or

but it doesnt. Its a sister function at best. Because:

=valueshown5("A"&eval(AU29),1)

WORKS!! & im happy about (I put the eval in to test. Its not part of the oppration of the fuction. It was just to test its scope/dynamism.

ftr in my tests, AU29 = =SUBSTITUTE(SUBSTITUTE(FORMULATEXT(BA19),"=JustAppear256(",""),",1)","") where justappear256 was just the same or similar or ValueShown5 or JustAppear33AG (which I reckoned upto last night when I got JustAppear33AG to work was the best, as it was alllowing the ability to store the 1st Argument/parameter somewhere and only work on its copy only. ok)

But, =JustAppear33AG(eval2(AU29),1) returns error. It must be to do with the way I am parsing/handling the parameters of JustAppear33AG (what I hoped was my better and more "functional for the future" fucntions, with posibilities to store the parameter/argument values so that other functions would refer to these (and not the appearance of the cell) code ) .



Im wondering if anyone can help me develop my code , and or make it work for when i out evals/or evaluatestrings in the parameter for the 2nd udf.

SO have closed the question yesterday (not asked by mysefl) but they let me work on it everyday since then. I just hope I don't get a typical SO answer here and actually get someone who can engage with me , and help make better the proof of concept and / or code.

 

[Spyros13]

justappear33ag or justappear33ag3 - which are the same - just copies of each other work but not with Eval's. See picture of calculation step:

However Valueshown5 or an earlier justappear22 both do work both on their own and with evals.

However Valueshown5 & JustAppear22 don't in the code read, extract and parse , their own formula's , the AG's do. So its the AG's I wan't to get to work with evals in the parameters.

Oh, hang on, one reads the parameters as text or evaluated text (the AG's) the others earlier one (ValueShowns) read the parameters fully evaluated and therefore can take evals.!! I get it !! No I understand my own code, I don't think I needed to ask anything!! Thanks !! ) **But** id like to get some advice on it or help for proper fucntionality of the AG version , definately need to improve the way im extacting the arguments and calculating on them. Any help please?

Basically would like to get

justappear33ag

working exactly like

Valueshown5

with the same abilties.

 

[Spyros13]

yes i know my question in the message content is different to the one of the message title. The title was my imagined end-goal. But for now id just like to get the JustAppearAG33 function work in the same way as the valueshown5 function.


But if someone wants to have a go at re-doing the whole attempt from the start, I wont say no !! [the question wasnt mine to begin with !! but I did find what it led me on to doing amazing so I give it that!]

p.s. why are SO's 'experts' so ... '''argghh' . Searched all over interweb internet for a similar question or macro , and couldn't find anything, so Im very intregued/proud of my attempts so far. BUt id like to develop.

 

[Spyros13]

Oh, I do need help with this function. Perhaps an explination. (if anyone could better help on the AG one Id appreciate it)

Because,

=LEFT(JustAppear33AG(AU29,1),1)

gives error but

=LEFT(valueshown5(AU29,1),10)

gives right result.
and

=LEFT(valueshown5(AU29,1),FIND("..",valueshown5(AU29,1),1))

gives right result .

These are all my own functions i made to a question from someone I don't know on SO (which was closed yesterday). ps. Note: AU29 is

 =SUBSTITUTE(SUBSTITUTE(FORMULATEXT(BA19),"=JustAppear256(",""),",1)","")

but it could be anything , even a 1-digit character or integer.

Note: Im more proud of the circumstances within which I made the UDF's in the first place / instance, more so than the UDF's themselves which are nothng special. Perhaps I should have been clearer /explicit on that. But really hoping to develop AG to make her work as the valueshown's do. I know im close.

 

[Spyros13]

pps. Important. The point of this UDF macro and column thing wasn't to get the cells text fitting the column widths perfectly at first. No. No way. The point of it was just to show proof of concept. I'd work on adjustitng the resultant text to the colunn widths more perfectly at a later time/date, once the macro's where fully viable.

 

[Spyros13]

ok. im kind of content happy with the fnc's.

Got JustAppearAG33 to work with eval using :

arg22 = chr(34) & Trim(Mid(formulacell6arg, Len("JustAppear33AG(") + 2, Len(formulacell6arg) - Len("JustAppear33AG(") - 4)) & chr(34)
    arg3 = Application.Evaluate([arg22]) 'Worksheets.Evaluate(Trim(arg22))

which resulted in output AS36 =JustAppear33AG(eval2(U29),1) =

eval2(U29) - ok

so atleast it read/executed the parameter . I would have liked it to act.execute on that eval2(u29) too which is

82738846​

But i can live with that. It only took me 1 or 2 steps to get it from it from the new JustAppearAG33, to that, so values arn't being lost!

But still Will work on the udf macro again at a later date / time (perhaps Rick Rothstein can help me?) to make it batter and. to enable it to do it all in only 1 step, from the udf.

Then ill work on fixing the text to column widths more accurately.

 

[Spyros13]

I know it will fail in instances where the 2nd parameter (toggleswitch) has a comma, like in an if functon like in justappear33AG(a1&a2,if(f1="yes",1,0)) because with the way I extracting the 1st parameter the extraction of the first parameter will then read "a1&a2,if(f1="yes",1 -which will produce an error (with the way im currently extacting it) & is wrong , but it was just basic steps to help show a solution does exist and that such a question can have a solution (& a udf one too) and it can be done. Lots more work to do on it, I know, But it was early days and kind of an open question. I do hope Rick Rothstein or others here can have a go at producing a more functional adaptable generic valid version, I will try too.