Replies

I’ve just noticed that when using the Folder measurement option with a very large folder I get the error response “Run-time error ‘6’: Overflow”. Would it be possible to remedy this or to provide an appropriate error message?

My thanks to all of you; I’m really bowled over with your excellent level of assistance. I intend to try to create an amalgam of the two concepts, combining the speed and simplicity of one with the generality of the other.

My thanks to both of you

Andrew,

Thank you for your help. I should have explained that I distribute my code among a number of users and cannot rely on their settings. Without changing my environment as you suggest your code I found would not work but the code below that I found in VBA Help did work. However it only leaves a message for folders and does not return a result. This was what I was attempting to change but my VBA skills are insufficient for the task. To my untutored way of thinking, if the value can be obtained to leave in a message, it should be available to return as a result, and for both folders and files. If you have the time, I’d be very grateful for your insights

Regards

Syntax
object.Size
The object is always a File or Folder object.
Remarks
The following code illustrates the use of the Size property with a Folder object:

Sub ShowFolderSize(filespec)
Dim fs, f, s
Set fs = CreateObject(“Scripting.FileSystemObject”)
Set f = fs.GetFolder(filespec)
s = UCase(f.Name) & ” uses ” & f.size & ” bytes.”
MsgBox s, 0, “Folder Size Info”
End Sub

Steve,

Absolutely perfect. Many thanks.

Geoffrey

Steve,

Thank you, I very much appreciate your advice, especially the Very Hidden part. It works well for Sheet1 but it’s case sensitive and as far as I can tell does not handle [book]sheet requirements. I’m a bit worried it won’t handle checking the status of sheets in workbooks other than the one from which I’m working. I’ve checked that if the macro language code selects the other workbook it then seems to work, but it would be more elegant to solve it in VBA as the macro language has so many limitations.

Again, thanks to both of you. It’s increased productivity in this area by a factor of sixty or more, and if I take the whole application, it’s whole increases in accuracy and productvity. That’s a great step forward!

I did change the “>256” to “>255” as I found it got stuck on 256 exactly. These underlying bugs are certainly tenacious!

My sincere thanks to both of you. I haven’t yet had time to check the suggested change but will do. The code works like a charm with ONE exception. It does not work for cells with 256 characters or more. I’m using Excel 2003. I know that there are some limitations at this number and it may be that it cannot be solved in this version of Excel. Even if that is the case, it’s a huge step forward for me, as I can go through and manually alter if necessary.

(My application seeks to identify names in text and to check for validity against a database. Most names are in bold. I do some manual adjustments (eg non bold to bold and vice versa) and the rest is code. The bit you’ve helped with so kindly is the one part I could not do myself.)

Rory,

Thank you. Unfortunately my knowledge of VB is very limited and I’d appreciate more assistance if possible. I’m hoping to convert all non-bold characters to spaces in situ (ie convert a selection of cells so that all non bold characters are spaces). If you have the time to show me a bit more I’d be very grateful.

Geoffrey

Thank you Hans. I’d not understood this subtlety. Thank you also for showing me there is no built-in function to calculate such a result.

Thanks to both of you. I think I’ll probably try to code up a primitive equivalent based on a one row Criteria Table with simple conditions (probably equal) only allowed.

regards

Geoffrey Howell

Hans,

Thank you so much. I’ve updated my Visual Basic functions into my ZMacros suite and modified the XLM Macros slightly. In the applications above that it’s just adding in the “A” onto the “Open”. It all works fine now thanks to you. Your work is truly excellent, and I appreciate it very much.

regards

Geoffrey

Hans,

Thank you very much. It works and that’s so encouraging. But I need to put this into a function and I’ve written the following code in place of your macro (leaving your lines prior to your macro in place).

Function ZFileOpenA( _
MyFile As String, _
MyDir As String)
Dim lngResult As Long
Dim strTarget As String
strTarget = ZFileJoinPath(MyFile, MyDir)
lngResult = ShellExecute(0&, “Open”, _
strTarget, 0&, 0&, SW_SHOWMAXIMIZED)
If lngResult <= 32 Then
ZFileOpenA = "Undefined Error"
Exit Function
End If
ZFileOpenA = True
End Function

Unfortunately it bombs out on the Function line and I am really out of my depth. (Previously and with your help I've now got functions that Open, Copy, Move and Delete any file and I can access these functions in XLM Macro 4). If I can get this section working then I should have a reliable suite of functions. I'm sure I've done something wrong that's very obvious. Any help would be appreciated.

regards

Geoffrey

Hans, Perfect! Thank you very much.

Geoffrey

Hans, Thank you. I’ve tried the following line:

ActiveSheet.FollowHyperlink Address:= “http://www.google.com.au/&#8221;, NewWindow:= True

and I can use error control to determine if it works or not. But I can’t get that line to work, though it works if I open a file within my computer. I assume I’m doing something wrong. Do you have any thoughts?

regards

Geoffrey