Posts by skepticologist

Well, I lucked into the solution. I had formatted the offending textbox as "Accounting". A little later I changed it to "$ #,##0.00" and there was no more problem. Sorry for the false start.

I've been an Excel developer for over 20 years and have never seen anything like this before.

I have a userform that calculates commissions. If a commission is from a sale, a "Total sale" textbox becomes visible. If from a lease, three textboxes becomes visible: one for the total square feet, another for price per square foot, and then third for the total price. The total price is the value in the first textbox multiplied by the value in the second.

Everything is normal when I enter the total square feet in the first textbox for a lease. When I enter the price per square feet in the second textbox, everything is OK until I press Tab or Enter. When I do, the textbox is populated with the following string: A1/19/19001/19/1900ou0ti0g.

I've replaced two textboxes with new ones but this keeps happening. I checked to see if there's a control source but it's blank. Each time when I double-clicked in the textbox the Click event appeared perfectly normal. It sort of looks like there's some code populating the textbox, but if there is I didn't write it and have no idea where it might be.

If anyone has seen anything like this, hearing about your experience would help restore some degree of confidence in my sanity. If anyone can give me an idea of what's causing it, I and my client will be eternally grateful.

I have a client who wants to automate his company's work audit process (currently done with clipboards and duplicate entry) that's the centerpiece of their TQM program, specifically with an app auditors can use to record data as they walk around the facility.

A couple of years ago I discovered CloudOn for iPad that actually ran even fairly complex macros in Excel and had ActiveX controls to boot. Unfortunately, it was bought by Dropbox in 2015 and to my knowledge never again saw the light of day.

So I'm desperately searching for a mobile application that will capture data (ideally with data validation functionality) and make it easily available to Excel. I know nothing about developing mobile applications, so it would need to be largely off the shelf.

I'll be deeply grateful to anyone who offers any information in this regard.

https://stackoverflow.com/questions/45244064/mobile-app-for-gathering-input-to-excel?s=1|2.6916

Re: Keeping VBA Library References

Thanks so much to both of you. I finally think I may be getting somewhere with this issue. Just to test my understanding:
- If I'm running developed applications on my own PC, i.e. against the references I've put in my library, there shouldn't be a problem as long as I've added the required references once.
- For distributed applications that use non-Excel applications, e.g., Outlook, Word, I should declare variables that refer to them as Object and then apply other data types as necessary when defining those variables.

Will that serve as a rule of thumb for distributed applications?

I'm running Excel 2016 in Office 365 Pro Plus and am having a problem I haven't been able to find anywhere else. Specifically, virtually every time I open Tools > References in the VB Editor, the references that are selected have changed. There are 4 specific references where this is a problem:
- Microsoft Visual Basic for Applications Extensibility 5.3
- Microsoft Scripting Runtime
- Microsoft Forms 2.0 Object Library
- IlydaUK Ribbon Commander v1.1 (third-party add-in)
One or more of the above 4 will be unchecked anytime I check after re-checking them.

I read one apparently related post that suggested making sure all were checked in the personal workbook. I checked the ones that weren't and that lasted only about a hour, including in the personal workbook.

I've also seen some posts that make reference to late vs. early binding but could never find any specifics about how to set that up.

Any additional suggestions will be deeply appreciated.

Re: Get the Meaning of Unfamiliar Symbols in Code

Thanks so much for enlightening me fellas. I knew this was the best hope of finding someone who had the information I needed. Of course, it's a little intimidating to realize something I thought of as so obscure was common knowledge to this crowd.

Re: Get the Meaning of Unfamiliar Symbols in Code

Not sure what happened to the code sample in that reply. Here's the second attempt and it's been previewed.

Dim count As Integer 
Dim fileNumber As Integer 
For count = 1 To 5   
   fileNumber = FreeFile()
   FileOpen(fileNumber, "TEST" & count & ".TXT", OpenMode.Output)
   PrintLine(fileNumber, "This is a sample.")
   FileClose(fileNumber)
Next

Re: Get the Meaning of Unfamiliar Symbols in Code

Thanks. Your # explanation makes sense because the app opens 5 different source workbooks. But I'm wondering why anything is needed in addition to nSourceFile being tied to the variable FreeFile (i.e., the next available workbook). Here's MSDN's FreeFile example which doesn't use any symbols, although I guess you could only have those 5 workbooks open:

[COLOR=blue]Dim[/COLOR] [COLOR=blue]count[/COLOR] [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR] 
[COLOR=blue]Dim[/COLOR] fileNumber [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR] 
[COLOR=blue]For[/COLOR] [COLOR=blue]count[/COLOR] = 1 [COLOR=blue]To[/COLOR] 5   
   fileNumber = FreeFile()
   FileOpen(fileNumber, [COLOR=#A31515]"TEST"[/COLOR] & [COLOR=blue]count[/COLOR] & [COLOR=#A31515]".TXT"[/COLOR], OpenMode.Output)
   PrintLine(fileNumber, [COLOR=#A31515]"This is a sample."[/COLOR])
   FileClose(fileNumber)
[COLOR=blue]Next[/COLOR]

As for the dollar sign, you could just use a conversion function to figure out what the data type should be.

It must have been a while since using those symbols was a common practice because I Googled every variation I could think of and came up empty, including MDSN.

My new project is to modify an application developed by a consultant who is currently in India, i.e. unavailable. I've looked at all the existing code and I'm OK with it, with the exception of a couple of symbols as shown below. Since the function they're in is relatively short, I've copied it in its entirety in the interest of context.

Function GetText(sFile As String) As String
'
Dim nSourceFile As Integer, sText As String




   ''Close any open text files
    Close
   ''Get the number of the next free text file
    nSourceFile = FreeFile
   ''Write the entire file to sText
    Open sFile For Input As #nSourceFile
    sText = Input$(LOF(1), 1)
    Close
    GetText = sText




 End Function

The mystery characters are the crosshatch in the line . . .

Open sFile For Input As #nSourceFile

. . .and the dollar sign in the next line down.

sText = Input$(LOF(1), 1)

I've literally scoured the internet, including MSDN of course, and came up with zilch in VBA code. A lot of you folks have been at this a lot longer than I have, so I'm hoping one of you has come across at least one of these symbols and can tell me what they mean. If so, I'll appreciate it greatly.

Re: Searching for a Solution to a Long-Standing Excel Problem

I don't remember when I've seen such a comprehensive response, and I genuinely appreciate the time you spent and, most of all, some excellent suggestions. In fact, you nailed me on several things that could be contributing to the problem. I'll definitely implement your suggestions, starting with keeping a running log every time the issue reappears.

As a small gesture toward returning the favor, Deb Dangleish on Contextures posted some code that gives you complete range information for every sheet in a workbook. I hope you enjoy it.

Once again, thanks so much for your response.

QUOTE=mchilapur;772758]Also you can try:

For example Excel can get confused about the actual range of your worksheets.
Press CTRL + END and see where the selection ends up.
Best case scenario it will be right underneath the the bottom-most right-most used cell. However, sometimes it will be way off of that with a lot of empty space to your actual data.
Select all the empty cells, then right-click and select Delete (this is different from pressing the DEL key!). Save your file and test again.
Do this for all the worksheets of your workbook to clear unnecessary “empty cell” bloat.

I already mentioned that formatting can get funky. And with more and more formatting it can get more and more problematic. If you were conservative with formatting clearing and resetting it shouldn’t take too long and might help.
* Definitely create a backup of the entire workbook beforehand.
For example if you have a header row with a certain formatting select the entire row, then on the Home tab in the editing group click Clear > Clear Formats. Then reset the format.
Actually, a good place to start might be the non-formatted cells! Probably most of your data entries are in the standard format. Select all of it and clear the formats to make sure it isn’t “made to look like standard format” ~ this will also clear data formatting (e.g. Time, Date) so this is something one has to keep in mind.

Finally I would clear the Formula error checking.
On the Formula tab there is a Formula auditing group. Click on Error Checking > Reset Ignored errors. In general, messy formulas and a lot of nested formulas create performance hits. Now, if there is some error in one of the initial formula it will be carried through all the nested ones too creating a lot of background work for Excel[/QUOTE]

I work as an Excel Developer and owe a lot to Ozgrid and stackoverflow, which have become my go-to sites over the years. Never in all that time have I read a post addressing my particular problem. I also just did a couple of searches with the keywords "performance" and "freeze" which got a few hits but nothing directly related to my situation. Specifically (and I'm not exaggerating), 4 to 7 times a day Excel freezes up on me. Sometimes, even thought I can't even select anything on a spreadsheet, I can save my work before closing out of Excel and restarting. Too many times, however, the freeze is accompanied by a message that "Excel has stopped responding". When that happens, the only way I've found to get out of it is to end the task in Task Manager, in which case everything is lost back to my last save.

This has been going on for literally years. It happens at work with Excel 2010 running on a less-than powerful Lenovo PC, and at home with Excel 2016 running on an HP Envy 750-xt with an Intel Core [email protected] GHz processor and 32 GB of RAM. It's happened on at least my last 2 personal PC's and in other work situations. Since I can't find a trace of the same kind of problem here or on stackoverflow, where you'd figure at least 1 or 2 of the thousands of experienced Excel users would be grousing about it, I'm beginning to think it's either something I'm doing very wrong or I'm carrying some kind of Excel curse. Unfortunately, Microsoft tech support hasn't come up with anything they'll admit to.

If anyone has any information on this or a similar problem and, even better a potential solution, I'll be eternally grateful if you'd reply. Thanks in advance.

I've got one of those VBA dilemmas I'm having a hard time figuring out. In general, I've been getting "Out of Memory" messages, and submitted an IT ticket asking about an upgrade to the 4 GB the machine I'm using comes with. But now I'm getting the following run-time error: "Method 'Find' of object 'Range' failed." When I click on debug, the following line of code is highlighted:

Range("A:A").Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Activate

The macro up to that point had been taking 20-30 seconds to run. When I put a stop on the line with the FIND method, the first part of the macro took less than a second to run . . . AND the FIND method went straight to "Summary" with no problem when I pressed the F8 key.

I've taken all the memory-saving steps I can find on Ozgrid, and I'm wondering if it's not really another kind of problem. I'm almost hoping it is because all the prospective users have only 4 GB of memory as well.

I will genuinely appreciate any information regarding similar problems, possible solutions and/or wild guesses.

Re: Excel Freezes for No Apparent Reason

Quote from S O;758430

Very odd - hard to tell without seeing. Perhaps this could be a start though

Thanks so much. I'm definitely going to check out the fixes. At the very worst I could rule out some more possibilities.

Re: Excel Freezes for No Apparent Reason

Quote from S O;758425

Do you happen to be running any macros when this happens? Or working with large data sets etc?

More importantly, what version of Excel/Windows are you using?

Negative on both counts. As I said, I haven't been able to link the freezes to anything. I'm running Excel 2013 on Windows 10 Pro 64-bit.

I've always experienced Excel freezing up intermittently, but thought I might be over that when I bought a PC with 32 GB of RAM. Well, I was wrong again. In fact, it seems like it's happening more often than before. I've read several posts on the subject, but they all seem to be able to tie the freezes to an event like running a macro, saving, etc. I haven 't been able to connect my freezes with anything I'm doing. Generally, I use the Task Manager to shot down Excel and start over again.

If anyone has any ideas how I can rid myself of this scourge you'll earn my highest esteem.

Re: Find Detailed Properties

That's good to know, but there's no connection between the elements in the dropdown and the properties they relate to. Maybe I'm just lazy, but what I'd really like to find is a list of properties with the elements related to them

Having worked with VBA for several years, it's hard to believe I've never got around to asking this question before, but here goes.

What I'm looking for is the detail around properties I look up in the Object Model. For example, the Range object has the property Borders, and there's generally an example that shows a bit more of the details of the property (e.g., Borders(xlEdgeBottom), then .LineStyle = xlContinuous, Weight = xlThin, ColorIndex = 3. What I'm trying to figure out is where to find ALL the parameters like LineStyle and ALL the options like xlContinuous. I've seen such tables before, but just can't figure how to get to them.

Any and all help will be greatly appreciated. Thanks.

Re: Workbook.SaveAs Eror Message with Alphnumerics for Filename

I really appreciate the suggestions, but turns out it wasn't a problem with Excel at all. In checking along the path, I discovered that the copies of files I save to my OneDrive still have the old SkyDrive as part of the path in their properties. I still need to wrestle that one to the ground with Microsoft, but in the interim I did a few tests of Workbook.SaveAs using the macro recorder and discovered I can save directly to OneDrive online with the URL to my account included in the path. Thanks again!

I'm about as frustrated as I've ever been with Excel, and I'm counting on some of the brilliant Ozgrid minds to resolve a really sticky problem.

I've written code for a part of an application I'm developing to copy the active worksheet, make a few changes to the new workbook, and saving it to a different path than the original workbook. Here's the line of code that's intended to save the new workbook:

ActiveWorkbook.SaveAs ("C:\Users\Daryl\Insurance\Medical\Medicare\Claims\2015\Claim Summaries\20150129 Matthew M Thompson MD Office Visit.xlsx")

Everytime I run it, I get a run-time error 1004 message that says "Microsoft Excel cannot access the file", followed by the path and filename. The filename is where the problem appears to be: ''C:\Users\Daryl\OneDrive\Insurance\Medical\Medicare\Claims\2015\Claim Summaries\1FDBE200'. As you can see, there are 8 alphanumeric characters (different each time) where the filename is supposed to be. When I step through the macro in the VBE Locals window, the filename displays correctly.

The code initially had references to formulas for the "2015" folder, and to three separate formulas making up the file name. I wrote it out letter-by-letter for testing purposes. I have now done an "Open and Repair" on the original file, and after confirming the problem occurred with other workbooks and file paths, both a "Quick" and "Online" repair of Office 2013 from Programs and Features. None of those has helped.

Any resolutions or even best guesses you'd care to offer will be greatly appreciated. Thanks

Re: INDIRECT Formula Insertion Problem

I owe you one, SO. I could've looked at the formula for another day and still might not have added those last two quotes.