Solved

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

Posted on 2016-08-03
  • VBA
  • Windows OS
  • MS Office
  • MS Applications
  • Visual Basic Classic
  • +1
22
110 Views
Last Modified: 2016-08-19
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
  • 10
  • 6
  • 3
  • +1
22 Comments
 
LVL 14

Accepted Solution

by:
DrTribos earned 400 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 83

Assisted Solution

by:oBdA
oBdA earned 100 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 14

Expert Comment

by:DrTribos
ID: 41741835
nice
0
 
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 83

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 14

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 14

Expert Comment

by:DrTribos
ID: 41743613
Did you try the custom clipboard code, any luck finding a different programmatic approach?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 14

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 14

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 83

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now