• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Upgraded forums - Old VBA codes & formula handling

Status
Not open for further replies.
shrivallabha

shrivallabha

Excel Ninja
Post upgrading forums, I have seen couple of things [maybe they are already noticed and being fixed] that have been affected little.

In old forums we posted codes with backticks (`) and those codes post migration have got some unusual guests in between. See this example:
http://chandoo.org/forum/threads/vb...cular-map-or-desktop-solved.11538/#post-67641
The quotes have been replaced by " [I know Chr(34) is quotes ;)]. Users will get errors if they copy the code and try to run it.

And sometimes the smileys intrude into the formula
See here. Sam's formula smiles at couple of places :D
http://chandoo.org/forum/threads/need-an-excel-formula-to-sum-data-basis-the-month.12043/#post-70610
I think this can be avoided if formula is posted in code tags. Maybe we should use code tags for formula as they are also a type of code ;)
 
I think our current thinking is that we clean as we go. Certainly if I post a link to a thread to try and help someone, I'll go and check the thread to make sure the codes/formulas are working. Looks like the following a the most often culprits
" is "
&#60; is <
&#62; is >
&#39; is ' (single apostrophe)
 
Hi, all!

I'd rather go towards a little conversion fix program for each issue.
Target issues are, for the time being:
- code within backticks (indentation)
- links to old URLs (check quoted)

I think that this last is more serious and important.

Regards!

SirJB7 said:
Just noticed that all links pointing to any topic or post what were migrated from the old to the new software weren't updated, and for the time being become unreachable.

Example.

In this post:
http://chandoo.org/forum/threads/excel-2007-security-best-practices.6348/#post-35991
there were these links:
http://chandoo.org/forums/topic/user-form-via-qat#post-28628
http://chandoo.org/forums/topic/user-form-via-qat#post-28748
http://chandoo.org/forums/topic/cell-protection-in-excel#post-22763
http://chandoo.org/forums/topic/cell-protection-in-excel#post-22788
that are no more available or reachable, turning the migrated history in useless stuff.

I think that the conversion of those links isn't very difficult for the topic part since their new URLs are like this:
http://chandoo.org/forum/threads/cell-protection-in-excel.4328/
so changing "forums" by "forum", "topic" by "thread" and adding "." plus the number that might be retrieved from <either a migration helper table, which I hope, or from a partial search into the actual table>.

I don't know about the post number, which seems to have been renumbered. Maybe there's another migration table helper, or in the worst case, there's always the last backup files that were migrated.

Reference to chandoo's topics and posts was widely used for all Ninjas, specially, and for other members too. Getting old/wrong links is like losing the history, besides not arriving to the solution.

Can something be done about it? May I help, if required any programming?
Click to expand...
 
Thank you guys for your replies.

If we can create a fix up code and be able to run it then it will be beautiful. Manual task will be challenging and will never give us a guarantee that all of the items are attended.

I will post if I find something more in this same thread.

Edit: Since code is said, I think if it is possible to invoke RegExp then probably we have a quick and dirty winner. An expression like &#[0-9]+; can catch all instances where we have this issue.
 
Thanks for bringing this to attention.

I wanted to have an automated solution for old links -> new links problem. But due to the new forum structure we have a threadID at the end of each thread. And doing automatic conversion seemed like a problem.

I will investigate this further and let you know.
 
@r1c 1
Hi!
If you confirm my assumptions of the ThreadID availability (where? Table or partial lookup?) and post renumber the code isn't hard at all, please just advise.
Regards!
PS: Have you read my related conversation of this last Saturday 14th?
 
@All
Hi!

Regarding this topic issue and the following link collateral damage:
http://chandoo.org/forum/threads/datedif-function-not-available-in-excel-2010.12307/#post-72251
on Tuesday I happened to have a meeting with an old friend who works (ha, when he does, it's easier to make me work) as webmaster, mostly with phpMyAdmin and MySQL, so after the Carlsbergs after the Carlsbergs that wet the lunch after our meeting, he dared to ask me if it was me who write frequently at an Excel site since he found my nick lots of times while google-ing for Excel issues, which is not his forte. I confirmed it was me and maybe I have a new client.

But as I didn't want to let him go without a couple of questions, I told him about the old forums, the spam issues, the migration and I showed him specifically these 2 topics (this one and the other linked). He listened to me without a word and then took control of my notebook and began playing a bit. After a few minutes of speaking to himself and cursing and damning against all, he asked me things about which I only knew the theory but haven't any practical experience or I only knew that a forum's software migration has taken place.

Summary, his comments are these:

a) Google indexing is not working.
Performing searches from a browser using the forums names should retrieve more than 15K entries, where we only got 6 pages. Then he entered to the WebMasters Tools of Google and in a feature that forces the re-index of any site (limited to 500 entries if not the owner) and after that new searches with the same previous arguments retrieved more than 30 pages.
His recommendation was to re-index the whole site.

b) XenForo URL redirection or rewriting seemed to be only taking care of the topic / thread number.
Putting this in Google search:
http://chandoo.org/forum/threads/excel-2007.3402
led us directly to the topic, but neither of these even appeared:
http://chandoo.org/forum/threads/excel-2007
http://chandoo.org/forum/threads/excel 2007
http://chandoo.org/forum/threads/excel
being led to the site page for error handling: The requested page could not be found.
And any of these:
chandoo.org forum threads excel 2007
chandoo.org forum excel 2007
led to the Google entries, but any with the wanted destination.
And any of these:
http://chandoo.org/forum/threads/excel-2007.3402
http://chandoo.org/forum/threads/excel.3402
http://chandoo.org/forum/threads/3402
http://chandoo.org/forum/threads/whateveryoumightwrite.3402
led us directly to the topic.
So what matters is only the topic / thread number, which is exactly the missing part in the searches, i.e., it's what we don't know a priori.
I almost forget: (partial) user names don't seem to be included in the search unless user has been addressed explicitly in the same post.

c) Existing links to previous topics / posts with the old values.
Maybe if having been used the redirection codes 301 (permanently moved) or 302 (found) or 307 (temporarily) or any other, original posts should have remained useful and it older links available.
https://en.wikipedia.org/wiki/URL_redirection
http://webdesign.about.com/od/http/qt/tip301v302redir.htm
Check the examples in this last link.

d) If no redirection, HTA or anything alike is possible.
He agreed with the method for converting the links described upwards, and from his personal point of view, the code for converting posts and links should be very simple. Counting on the availability of the migration table with the new thread number and the old posts table for renumbering.

I don't know if this will aid Chandoo, the webmaster or whomsoever, to make this site regain its accessibility and friendliness, but despite of my previous offer of converting old URLs there's nothing much more that I can imagine to do.

Hope it helps.

Regards!

PS: I'm sure I understood everything when my friend explained it to me, just hope that I've been capable enough to transmit and rewrite everything here. So if there're any mistakes, well, it's due to Carlsberg. :)
 
Thanks for the discussion and sharing it with us SirJB...

Let me ask my web site admin about some of these and see how we can quickly fix.

Regarding mass redirection, I have investigated and found that it would drastically slow down our servers. Since we have more than 15000 old links, one approach recommended by my admin is that we fix as we go. Very few people go to old forum pages. Most traffic is on the new threads created and these are clean.
 
Most people except the Ninja's who are always looking for old content to use as examples
 
@r1c1
Hi!

AFAIHBT, aka have been told, today, redirection isn't available yet since old topics/posts with old links aren't yet reachable, it'd have been the easy and temporarily 1st option at migration time. So the issue is circumscribed to 2 points:

1) Update old embedded links to old chandoo's URL:

a) Topics from old format http://chandoo.org/forums/topic/cell-protection-in-excel/ to new format http://chandoo.org/forum/threads/cell-protection-in-excel.4328/

b) Posts from old format http://chandoo.org/forums/topic/cell-protection-in-excel/#post-XXXXX to new format http://chandoo.org/forum/threads/cell-protection-in-excel.4328/#post-YYYYY )

And this 2 require is a very simple program. If your web site admin needs support on this, please advise.

2) Google indexation:
Of old and new threads, that's to say, the whole site.

If not, I see 2 problems:

a) As Hui correctly pointed out, Ninjas and Active members are the most harmed by don't being able to find things yet done and just lead new users to those old links.
Are they supposed to re do again all that they've done before or that they know that someone did? If yes, you'd better check funding all the Black AmEx issued yet.
And what about new users searching anything? Should they be treated like Google which their bubble filter algorithms that shows personalized searches and not all of them? In this case they(all of us)'ll be bubbled to the new threads almost exclusively?

b) What about the history of Chandoo forums? Will it be buried until an Indiana Jones appears and find the lost ark and post a photo of the animal, sorry, the related link? Or will it be moved to the Deep Web?
If so, maybe someone (not me, I don't know anything about it, as my lawyer told me to say) could write a sticky thread to make Tor browsing easy?

Just to put it on black font over white interior.

Said so, as always, here I am. And at least many more, so... here we are. Could we do anything about all this that annoys us every day, each time we try to find anything? Please tell us.

Regards!
 
I have written one small routine for cleaning up the copied code based on what I suggested at post#4.

It basically strips off all parts like &#34; with their expected expression in VBA code. I have provided comments in the code and it is open for all to see. So please feel free to modify and improvise it as you like as it is minimum code without error handles. We can use it as long as the old codes are not straightened up.

In the workbook, you should find instructions for usage.

Code: 
Option Explicit
'\\ Usage: For replacing pattern &#39; with actual character
Dim objRegExp As RegExp
Public Sub CleanCodeRoutine()
Dim objClpBrd As DataObject
Dim varClDataIn As Variant, varClDataOut() As Variant
Dim strOutput As String
Dim i As Long

'\\ Get the copied information on clipboard and store it in array for further use
Set objClpBrd = New MSForms.DataObject
objClpBrd.GetFromClipboard
varClDataIn = Split(objClpBrd.GetText(1), vbCrLf)

'\\ Redim output Array for loading the processed output
ReDim varClDataOut(UBound(varClDataIn))

'\\ Process Data line by line and finally load it in a string
For i = LBound(varClDataIn) To UBound(varClDataIn)
    varClDataOut(i) = strCleaned(CStr(varClDataIn(i)))
Next i
strOutput = Join(varClDataOut, vbCrLf)

'\\ If we have some data then put it in Clipboard and inform user
If Len(strOutput) > 0 Then
    objClpBrd.SetText strOutput, 1
    objClpBrd.PutInClipboard
    MsgBox "Cleaned Data is copied to clipboard!", vbInformation
End If

Set objClpBrd = Nothing
Set objRegExp = Nothing
End Sub
Public Function strCleaned(strOriginal As String) As String
Dim vExp As Variant
'\\ Create RegExp and do its settings
If objRegExp Is Nothing Then
    Set objRegExp = CreateObject("VBScript.RegExp")
    With objRegExp
        .Global = True
        .MultiLine = False
        .Pattern = "&#[0-9]+;"
    End With
End If

'\\ Process through each match found
strCleaned = strOriginal
For Each vExp In objRegExp.Execute(strOriginal)
    strCleaned = Replace(strCleaned, CStr(vExp), _
    Chr(CInt(Replace(Replace(vExp, "&#", ""), ";", ""))))
Next

End Function
 

Attachments

  • VBA - Chandoo Old Code Cleaner V 1.0.xlsm
    176.1 KB · Views: 56
What the......

I just tested your code Shrivallabha - this post slipped quietly past me and it seems many other members of the forum. This is straight up….Brilliant!!!

You have taken your own time to come up with some stunning code to enhance the forum and even if it is not used I salute your endeavours and the purity of the coding. Well done!!!


Take care

Smallman

ps-Sorry just hitting Like would not have been enough for me on this particular post.
 
Thank you guys. I figured that even if you knew what to "find and replace", it'd still be a bit boring work. It is my token of appreciation for this wonderful community.

I was in dilemma as to where to post this. People normally don't go to sticky posts unless they have to :) But posting somewhere else would've lost continuity and context.

Plus, I could play with RegExp which I don't [have to] use normally. I'd imagine, as RegExp comes through VBScript it can be used by the board coders as well for cleaning up the data as well.
 
@r1c1
Hi!

I hope you're doing well, since I didn't have any notice from you in the last weeks except for the newsletters. BTW, I wish that you recorded significant progress in your new VLOOKUP book, we're all eager to give a look at it.

I actually feel a bit disappointed each time I:
a) Have to find an old thread to post its links so as to guide new members or answer new repetitive questions.
b) And when I do, not just at a first search as it was before and should be now too, then have to reformat manually or by shrivallabha's method or by own procedures the earlier posted code. This is a sample which I just finish to work on it. Again.:
http://chandoo.org/forum/threads/sorting-selected-rows-in-a-table.12864/#post-75627
and this is the sublink which I had to fix so as to be readable for the OP:
http://chandoo.org/forum/threads/alphanumeric-sorting-solved.10611/#post-61366

So my humble question (I know that it's repeated but it still unanswered) is regarding to this:
r1c1 said:
Thanks for the discussion and sharing it with us SirJB...

Let me ask my web site admin about some of these and see how we can quickly fix.

Regarding mass redirection, I have investigated and found that it would drastically slow down our servers. Since we have more than 15000 old links, one approach recommended by my admin is that we fix as we go. Very few people go to old forum pages. Most traffic is on the new threads created and these are clean.
Click to expand...
and this:
Hui said:
Most people except the Ninja's who are always looking for old content to use as examples
Click to expand...
and this:
SirJB7 said:
a) As Hui correctly pointed out, Ninjas and Active members are the most harmed by don't being able to find things yet done and just lead new users to those old links.
Are they supposed to re do again all that they've done before or that they know that someone did? If yes, you'd better check funding all the Black AmEx issued yet.
And what about new users searching anything? Should they be treated like Google which their bubble filter algorithms that shows personalized searches and not all of them? In this case they(all of us)'ll be bubbled to the new threads almost exclusively?

b) What about the history of Chandoo forums? Will it be buried until an Indiana Jones appears and find the lost ark and post a photo of the animal, sorry, the related link? Or will it be moved to the Deep Web?
If so, maybe someone (not me, I don't know anything about it, as my lawyer told me to say) could write a sticky thread to make Tor browsing easy?

Just to put it on black font over white interior.

Said so, as always, here I am. And at least many more, so... here we are. Could we do anything about all this that annoys us every day, each time we try to find anything? Please tell us.
Click to expand...

Have a nice weekend.

Regards!
 
My dear post:
Today, November 28th, I want to celebrate your first month of quietness, so have this little present. I hope you enjoy it and I wish you it's the last one.
To your anwser! That comes soon.
My best regards!
torta_de_cumpleanos_de_1_ano_tarjeta_postal-ra01ba026133a4e17a8ade32fd8be6d30_vgbaq_8byvr_324.jpg
 
@SirJB7
Hi, myself!
So long...
Silence. What is silence? What does it mean? Pages and books were written about it.
But to put it simply in my own words (if the dictionary lends them to me), silence sometimes is prudence, wisdom, compassion, diplomacy, gist; others it's cowardice, lordliness, impiety, rudeness, sloth.
Regards!
 
Status
Not open for further replies.
Back
Top