?
Solved

vba is buggy because of corrupted user profile. Any fast way to fix it?

Posted on 2016-08-03
28
Medium Priority
?
180 Views
Last Modified: 2016-12-16
This is a follow up to an earlier question.

After upgrading to windows 10 most of my many vba programs work fine. BUT, Ms Office 2010 programs that use a Dataobject to modify the clipboard always end up with two question marks on the clipboard. (Access, Word, Excel and Outlook are all affected.)

The programs work fine in a new created user profile named domain\rberkeFixed.

If I use rberkeFixed in the future, I must first waste about 6 hours reapplying 5 years of desktop customizations; moving outlook; and reprogramming every vba, vbs and .bat file that says <if username = "rberke" then ......>.

Clearly, the "safest" solution is to use the rebuilt RberkeFixed profile.  But, I worry what happens when my 10 coworkers convert to Windows 10.  They use many of the same vba programs, so profile corruption might cause the same problems. So, I want to evaluate other solutions that might be "almost as safe".

Does anyone have other suggestions that I could try that will clean the profile but leave most settings untouched? For instance, one URL suggested I use Regedit to change
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\S-1-5-21-xxx-xxx-xxx\refcount to be 0.

While that regedit did not work, perhaps something similar might ???

rberke
0
Comment
Question by:rberke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 7
  • 4
  • +1
28 Comments
 
LVL 15

Accepted Solution

by:
DrTribos earned 1600 total points
ID: 41741457
Well I can safely say that this is NOT a new problem introduced by Win10.... I've seen it for XP, Vista, Win7, Win8, and now in Win10.

It happened to me in Win7, it was a pain.  It hasn't happened to me since (s#!t - jinxed myself, right?).

I wait with bated breath for the solution you ask for.  It always pains me to tell my clients that they need to create a new user-profile because Word isn't behaving as it should....

As a workaround, you might be able to use a custom clipboard as described here: https://social.msdn.microsoft.com/Forums/office/en-US/ee9e0d28-0f1e-467f-8d1d-1a86b2db2878/a-clipboard-object-for-vba-including-microsoft-word?forum=worddev
0
 
LVL 85

Assisted Solution

by:oBdA
oBdA earned 400 total points
ID: 41741812
You're not the only one, and it's not worth it removing your profile. Even if you recreate your profile from scratch, In all likelihood it will come back.
I've had the same issue (frequently, though intermittent), and came across this thread:
DataObject .setText and .putInClipboard sequence puts invalid data (Hex 63 characters) in clipboard
https://social.msdn.microsoft.com/Forums/en-US/48e8c30c-24ee-458e-a873-a4e6e13f5926/dataobject-settext-and-putinclipboard-sequence-puts-invalid-data-hex-63-characters-in-clipboard?forum=isvvba

This is linked in the thread, and it resolved the issue for me:
Send Information to the Clipboard
https://msdn.microsoft.com/en-us/library/office/ff192913.aspx
1
 
LVL 15

Expert Comment

by:DrTribos
ID: 41741835
nice
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 5

Author Comment

by:rberke
ID: 41742782
"if you recreate your profile from scratch, In all likelihood it will come back."

This will be my 3rd domain\Rberke rebuild in the last 8 years so I agree that some form corruption is likely to come back, especially if I upgrade my operating system again.

BUT each of my corruptions had different/random symptoms, and none of them had the dataobject problems.  So, I conclude that the my specific DataObject corruption is unlikely to reoccur.

If the dataobject bug reappears on the rebuilt profile, I might use your workaround as a last resort.
0
 
LVL 5

Author Comment

by:rberke
ID: 41742791
oBda: you say you have had the same problem intermittently.  Were they always in the same user profile and machine?  Did you encounter any other vba weirdness, or was it only with dataobject?

I am "lucky" in that my problem is 100% wrong in four Office applications (Word, Excel, Outlook,Access).
I say "lucky" because that means any "profile rebuild" solution is easily tested -- it will either work or it won't.

Bob
0
 
LVL 5

Author Comment

by:rberke
ID: 41742929
obda:  I have developed the following diagnostic routine for Excel.  Could you please run it on your system and let me know what happens

I intend to put this into the company's GlobalShared.xlsm workbook.  That way whenever a user profile gets the corruption we will immediately know about it.

Sub TestCorruption()
Dim x As Object
Set x = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' late binding to dataobject
x.SetText "test"
x.PutInClipboard
Application.Wait (now + TimeValue("0:00:02")) ' this test only works in Excel, even though the corruption affects all office apps
x.GetFromClipboard
If x.GetText <> "test" Then
    MsgBox "vba is corrupted"
Else
    MsgBox "vba is not corrupted"
End If
End Sub
0
 
LVL 85

Expert Comment

by:oBdA
ID: 41743257
I don't think your profile is corrupt, and that's what I mean when I say that even with a new profile, Excel's clipboard issue is likely to come back.
The machine where I had the issue doesn't exist anymore, but it would show up on some users, sometimes go away on a logout and re-login, stay off a while, come back, ... And the two seconds you wait were exactly the time the clipboard content was still available, before it disappeared to Bit Heaven or wherever those things go to.
In the link above, you've seen the part about 'after a very useful exchange, MS determined the cause to be 'most likely a flaw in our product''?
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41743346
I'm going to disagree, at least to a small extent. In my experience this issue and many others can be permanently 'fixed' with a new profile.

Caveats.  Where ever possible I try to program around issues like this. I'm not going out on a limb to say a profile renew is the best or only way to fix issues, but who knows what other issues are lurking in the background that also get 'fixed' as a result.

Finally, the whole profile thing doesn't make any sense to me - but it seems to work.

My 2c
0
 
LVL 5

Author Comment

by:rberke
ID: 41743612
odba: So far, all of my Excel vba programs work fine in the new profile so I am pretty sure corruption was the cause.  I still have a boatload of customizations to the desktop applications but  I am pretty confident that the vba will continue to behave for many months.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41743613
Did you try the custom clipboard code, any luck finding a different programmatic approach?
0
 
LVL 5

Author Comment

by:rberke
ID: 41744427
That custom clipboard code works fine.  But I am not going to use it because I am convinced the problem is due to a corrupted profile.

More importantly, I have no reason to believe this corruption ONLY affects Dataobjects.  It is quite possible there are other subtle problems that may show up in the next few weeks. For instance once or twice a day my desktop freezes for up to a 2 minutes. Perhaps that will be corrected by resetting the profile.

Nonetheless, API code workarounds are tangent which I no longer wish to discuss. This question is still looking for ways to fix a corrupted profile short of completely rebuilding it.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41744857
Anecdotally, I don't think it is possible. I've seen so many questions where corrupt profile was blamed and rebuild has always been the solution. 😣

I'm adding a request for attention - would be glad to be proven wrong.
0
 
LVL 5

Author Comment

by:rberke
ID: 41745614
I agree with DrTribos that rebuilding the profile is "safest" approach, and I will do so.  But, I will keep the corrupted profle available in case somebody posts "almost as safe" method of resetting the profle.

Also, to protect against future occurrence of the problem, I created the following code.

All of our users have the program in XLSTART magic.xla so the code automatically runs the first time they open Excel every day.  

Option Explicit ' this must be the ThisWorkbook portion an xla workbook n the XLSTART folder
Private Sub Workbook_Open()
	Call TestCorruption
End Sub

Option Explicit ' this must be in a standard module, but it can be called from thisworkbook.workbook_Open
Sub TestCorruption()
Const file = "c:\aatmp\onceaday.txt"
Dim x As Object, f As Long
Static moddate As Date
    On Error Resume Next
    If moddate = Date Then Exit Sub
    moddate = Int(FileDateTime("c:\aatmp\onceaday.txt"))
    If moddate = Date Then Exit Sub
    Kill file
ON ERROR GOTO 0
    f = FreeFile
    Open file For Output As f
    Close #f

    Set x = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' late binding to dataobject
    x.SetText "test"
    x.PutInClipboard
    MsgBox "Once a day integrity test. Please click OK." ' msgbox is better than application.wait for detecting the corruption    
    
    x.GetFromClipboard
    If x.GetText <> "test" Then
        MsgBox "vba is corrupted"
    Else
        MsgBox "vba is not corrupted"
    End If '
End Sub



 

Open in new window

1
 
LVL 15

Expert Comment

by:DrTribos
ID: 41745759
I once proposed collecting examples of vba that test for a corrupt profile - the idea was poo-pooed by some but I'd still be keen to start building a collection of test.... up for it?
0
 
LVL 25

Expert Comment

by:SStory
ID: 41756503
Instead of doing if username=...
How about changing that to do a msgbox on username for the screwed up converted one to see what it is actually comparing.  Maybe it isn't what you think.  I would approach it by assuming nothing, debugging the code a little like this to see what it is doing and go from there.

Is this on a domain? Are there any other ways you might solve the problem?
0
 
LVL 5

Author Comment

by:rberke
ID: 41756828
You ask "Are there any other ways you might solve the problem".  

Let turn around and ask that question of you.  When a domain is corrupted, is there any alternate way to solve the problem? (The only way I know is to rebuild the profile from scratch.) That really was what I intended to ask when I started the problem.

And finally let me show the 3 lines of code that work in one profile and fail in another.
dim x as new dataobject
x.settext "test"
x.putinclipboard

I will close this problem tomorrow morning, as I think  further progress is unlikely
0
 
LVL 25

Expert Comment

by:SStory
ID: 41756976
rberke,
I'm sorry if I offended you by asking questions. I am trying to help you out. Your comment said,
If I use rberkeFixed in the future, I must first waste about 6 hours reapplying 5 years of desktop customizations; moving outlook; and reprogramming every vba, vbs and .bat file that says <if username = "rberke" then ......>.

So I thought...what if username isn't properly returning or is giving something unexpected?  You might try outputting that variable value to be sure it is what you think it should be on the systems if fails on to be sure that is NOT the issue. That is all that I was asking you to do.  I really did not read anything about you corrupting a domain profile. You only mentioned that you upgrade to Windows 10 and had this issue on a machine. Not everyone actually runs a domain so my questions were valid and my troubleshooting step easy enough to test.
0
 
LVL 5

Author Comment

by:rberke
ID: 41757076
No offense taken.  I appreciate all attempts to help.

The bugs are not associated with my username, so your advice does not really apply.  

Thanks to everyone, and I am now closing this question.

The "correct answer" is that there is no way to simply reset a profile.  Which is what DrTribos originally said.
0
 
LVL 5

Author Comment

by:rberke
ID: 41762662
I continue to research this issue, and want to report that my earlier conclusions might be wrong. My "corrupted" profile might not actually be corrupted after all.


My integrity test code sometimes finds corruption immediately after a reboot and sometimes finds no corruption.   (Of course randomness is often a symptom of a corrupted profile, but I don't want to jump to conclusions.)

I am modifying my integrity test code and will continue my research.  This question is closed, but experts are welcome to post further comments. Nonetheless, I will not respond to them until I have figured things out.
0
 
LVL 85

Expert Comment

by:oBdA
ID: 41762697
<cough>https:#a41743257</cough>
0
 
LVL 5

Author Comment

by:rberke
ID: 41763026
I know, I know.  I will give you props in a few weeks. Until then try some cough drops.  ;-}
0
 
LVL 5

Author Comment

by:rberke
ID: 41925760
I am moving toward a final resolution of the Dataobject corruption problem  that caused the clipboard to get two question marks ?? (Actually the clipboard did not have question marks, it had two Unicode UFFFF characters.)

About 3 months ago I created a new SBS user named RberkeNew and an associated profile MyDomain\RberkeNew on my laptop.

I still use \RberkeOld most of the time but periodically perform corruption testing with both profiles.

During that 3 months \RberkeNew  never displayed dataobject corruption, while \RberkeOld "randomly" displayed corruptition.

But, a week ago the randomness went away: I discovered that \rberkeOld corruption was always triggered if Windows Explorer had any user folder (like c::\) is open for viewing.   Corruption never occurred if I close every user folder.  Only user folders matter, "special folders" like the Windows  "quick access" have no effect.

Since that discovery, the randomness is gone: every test on both profiles iscompletely repeatable.  If I want to see corruption on \rberkeOld, I simply open c:\ . My Excel macro always says "corruption was found".  No matter what I do, I cannot get \rberkeNew to exhibit the corruption.

Therefore, I tentatively conclude that switching to a new profile is a decent solution to the corruption problem.  I plan to permanently switch to \rberkeNew.  If the corruption remains gone for a few months, I will have more confidence that things are really fixed.

Rberke
0
 
LVL 5

Author Comment

by:rberke
ID: 41925801
By the way, the Excel macro that tested for corruption became quite complicated because of timing issues.

For instance .putInClipboard immediately followed by .getFromClipboard sometimes showed corruption and sometimes did not.

To make the test more reliable, II had to use Application.Wait (Now + TimeValue("00:00:02"))

Even that was not totally reliable, and sometimes the corruption would not appear, so the routine automatically repeated the test a second time.  But, it is now so complicated, only a masochist would want to use it, so I am not posting it here.

Nonetheless, the routine is now extremely reliable, as long as \rberkeOld displays a folder like  C:\ is displayed, the routine warns me of corruption. If no user folders are display, it never finds any corruption.
0
 
LVL 85

Expert Comment

by:oBdA
ID: 41925819
"it is now so complicated, only a masochist would want to use it" oooooooooor .... you could have just used the code linked in https:#a41741812 to read the clipboard ...
0
 
LVL 5

Author Comment

by:rberke
ID: 41926008
You misunderstood my recent comment.


I long ago removed dataobjects from every program and replaced them with the APIs  that you recommended.  Thanks for that link #41741812

But, I kept dataobjects in one "checker" program.   This checker program runs automatically a dozen times a day and does the following:  

    1) get status of dataobjects (i.e. "Working" or "NotWorking", and store the status onto disk so previous status is available after a reboot.
    2) if status has changed from previous status then msgbox "dataobjects are now " & status
    3) the result is a program that is silent for weeks on end, but quickly alerts me to changes in the status of dataobjects.  

This program was necessary to either confirm or refute [b]two intriguing statements oBdA made 3 months ago:

(1) I don't think your profile is corrupt, and  (2) that's what I mean when I say that even with a new profile, Excel's clipboard issue is likely to come back.[/b]


So, in order to verify your first statement, I created a new profile, RberkeNew, and found that the dataobjects worked fine.  So that disproved your statement #1.

But, your statement #2 that the problem will come back is much harder to disprove.  It may take years before I am really sure your are wrong.


When I permanently convert to the RberkeNew profile, the checker program will continue to run a dozen times a day.  If the dataobjects ever stop working, I will know it almost immediately.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41926151
My hat comes off to you sir....   is your chunk of code above the latest refinement, if not can you share?

The most recent profile corruption that I came across was annoying... not in the least because the # was added by the VBA IDE.  Yes I found a profile that crashed on line 6 because 12# is the wrong data type; that's 1 profile out of 100s.

' PURPOSE:
' Check if testToPass throws an error
Function testToPass() As Boolean
    On Error GoTo errh

    If Application.Version = 12# Then
        testToPass = True
    End If

lbl_Exit:
    Exit Function

errh:
    Err.Clear
    testToPass = False
    Resume lbl_Exit

End Function

Open in new window

0
 
LVL 5

Author Comment

by:rberke
ID: 41928881
Well, I will share it, but I suggest that only a truly deranged person would want to use it.  And, a lot of the comments in the code were written weeks ago, and may actually be wrong.

It would take me way too long to explain why things ended up the way they were, plus I used preexisting functions like GetClipBoardType  and Ondisk that I don't feel like explaining.  so I am just posting enough portions of the code so that you can see the main dataobject issues.

good luck at understanding it.

        Else
            .txtCorruption = "no corruption"
            If OnDisk("StatusCorrupt") <> "UnCorrupt" Then
line2a:                     '      MsgBox "Corruption is now gone. FIGURE OUT WHY"
line2b:                     ' Stop  ' line1 returns "True" which implies NoCorruption. BUT BUT BUT
                 ' when the line2: Stop is executed, a ctrl V shows ?? which means the corruption is still there
                 ' In the milliseconds between line1 and line2 the corruption appears.
                 ' Perhaps the .getfromclipboard runs asynchonously?
                 ' to test this theory, I commented out line2a and added line3a
                 Application.Wait (Now + TimeValue("00:00:02"))
                 test.GetFromClipboard
                 newClip = test.GetText
                 If Not newClip Like "test*" Then
                     MsgBox "corruption was gone but returned in less than two seconds"
                 Else
                     test.SetText "test" & Rnd
                     test.PutInClipboard
                     Application.Wait (Now + TimeValue("00:00:02"))
                     test.GetFromClipboard
                     newClip = test.GetText
                     If Not newClip Like "test*" Then
                         MsgBox "corruption was gone for two seconds, but returned after .putinclipboard"
                     Else
                         
                        If LCase(Environ("username")) Like "bob.berke*" Then MsgBox "corruption is now gone. FIGURE OUT WHY"
                        Call OnDisk("StatusCorrupt", "UnCorrupt")
                     End If
                 End If
             End If
             If oldClip <> "" And Not oldClip Like "test*" Then  ' if there is no corruption leave new "test.1234" on clipboard
                 SetClipboard (oldClip) ' 11/1/2016 restore original clipboard
             End If
        End If
' ///////////////////////////////////////////////
        If oldClip <> "" Then
            If CODEW(oldClip) = "UFFFF" Then
                MsgBox "clipboard originally had UFFFF. It has been cleared"
                SetClipboard ("")
            End If
        End If
        Set test = Nothing

    '

    End If
' //////////////////////////////////////////////////////////////////////////////////////////////////////
    .Show ' Display the Master Menu user form
ee.jpg
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
New style of hardware planning for Microsoft Exchange server.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question