Posts by Goppi

    Re: VBA Find function sometimes not working

    Quote from StephenR;608344

    Specifying all the parameters might solve the problem. If you record a macro of using Find it will generate all the necessary syntax which you can then paste into your code. If that doesn't do it, a sample workbook would help.

    Hi Stephen,

    Thanks for your reply. I tried adding some of the parameters that I though could make a difference, but not all. I'll look into this and will try some more. A sample workbook unfortunately won't help. I have about 10 Excel files with heavy VBA code which are run every day and those have hundreds of Find functions - none of those are causing any problems. Even if I take the one file that currently causes sometimes an issue and I try it on my PC, I can't replicate the issue. The only difference is that on the one PC where this phenomena appears, a few other VBA programs are executed beforehand. Hence my thought that Excel might keep some Find parameters in the memory or that Excel can't handle that many Find calls...

    I'll try to specify more parameters and see if it makes any difference.

    I welcome any other hints, ideas or background knowledge on how Excel managaes Find methods, results and parameters :)


    Re: Modified Formula/Macro for Text

    Assuming you want to indicate with your last sentence that you need to use Option 1 as per jindon, aka you copy the number and want to paste a text with the pre- and post-fix - how do you want the vba code to distinguish between fax numbers and any other text you copy/paste into Excel?


    To make the story short, the find function sometimes doesn't find a the value in a range though it's there. However if executed on a different PC, it might find the value. I'm assuming it has to do with a state fo the Find feature that Excel keeps from a previous find (similiar to pasting text into Excel that based on previous text to column settings might or might not be correctly split into multiple cells.)

    A typical VBA code I'm using looks like this:

    Long Story - the VBA codes (multiple files) are executed every single day since about 2 yrs without a problem. About 3 months ago, suddenly one of the Find functions didn't find the cell anymore. The same happened on a second PC at exactly the same position in the code at exactly the same value - but not on a third PC. Back then due to time constraints, but also the small size of the repetitions I decided to implement a work-around by using a loop instead of getting to the bottom of this. The problem moved than from one Find code to another one in the same file. I used the same work-around and we didn't experience any problems until 2 weeks ago. Since 2 weeks it happened only on two days and only on one PC that the Find function again doesn't seem to find the value. When I ran the same code on a different PC on the same data, no issues. Thsi time, however I would delay the whole process quite a lot if I would implement the work-around and I probably only move the issue further to the next Find call. Hence I'm trying to find out what the cause could be.

    Did anybody come across such an intermittend behaviour? Might this be related to previously executed Find functions/methods?

    Thanks in advance,

    Re: Slow Update Of Edited Links

    It's not a Hyperlink nor a code that updates the values. It's a simple link between Excel files.

    Yes, the user replaces the folder in the link by Edit>Replace.
    I thought it might make a difference by replacing the folder-name by a VBA code, but no, it didn't. It seems that Excel is verifying every single link when changed. And that doesn't change when done by a macro. I'll think about alternatives, but also if it's possible somehow to stop the validation/updating mechanism in Excel until all links have been changed and than update them all at once.

    Thanks for your comments.

    My business has a significant number of Excel sheets interlinked. One of them has about 10.000 links to about 60 files. Those 60 files are within a sub-folder and once a quarter the business needs to replace all 60 files with a new set. To establish this, they replace the sub-folder name in the link with the new name. To do this on 10.000 links takes about 72 minutes because Excel seems to open up a file for every updated cell. We tried to stop this by changing Excel to calculate manual, disabled "Update remote reference", but this didn't improve anything.

    We have similiar issue when opening/updating the master file - it takes around 3 mins.

    Besides that a proper database is certainly better for this requirement, is there anything we can do to improve the performance of the replace method. I was thnking about stopping Excel from validating/updating each single cell and after everything has been replaced to do it in one go without Excel opening and closing 10.000 times a file.


    Re: Remembering And Setting Position Of Commandbar

    Quote from Andy Pope

    go to the object browser and search for CommandBar
    You will see the object as a bunch of properties you could check and apply to the new commandbar.
    Stuff like left, top, Position etc.

    I guess my question deserves this kind of answer. I was hoping that somebody had some well thought-out code already in use. Otherwise I will have to play around with Left and Top.


    Hi everybody,

    I have a few Excel 2003 worksheets with custom commandbars. I usually create them in VBA during the opening of the workbook. In case the commandbar is still there due to f.i. a fault in the code or anything else, I first delete it. So a typical sequence in The Workbook_Open sub looks like:

    On Error Resume Next
    On Error GoTo 0
    Set cb = CommandBars.Add("Test", msoBarTop, False, True)
    With cb
      .Visible = True
    End With

    The issue with this is that when I add the commandbar, it just adds it at the bottom of the top bar and not where I moved it the last time I used the file. I also have one excel workbook that creates multiple commandbars in the Top bar - but one under each other - ideally I would like to have them positioned next to each other.

    Is there any attribute that I could read out before deleting the commandbar and set when I create it?


    Re: Apostrophe Causes Error

    It's not VBA that has the issue with recognizing the string correctly, but the function GetObjects when it processes the string. Therefore the chr function wouldn't make any difference - or in other words if you test this in the Locals window you will see that the string with and the one without the Chr function gives exactly the same results.


    Re: Apostrophe Causes Error

    Hi, the issue you describe is unrelated to the one I have. I'm not writing anything into a cell with the getobjects method. That would come later. But the Getobjects method causes an error because it thinks that the first parameter has finished wiht the second apostrophe. Or in other words this is how the string should be interpreted:

    Exchange_Mailbox.LegacyDN = '/O=TEST/OU=TEST/cn=Recipients/cn=co'brien'

    ... basically the text between LegacyDN and the next parameter as one string - but this is how it does inteprete it:

    Exchange_Mailbox.LegacyDN = '/O=TEST/OU=TEST/cn=Recipients/cn=co'

    which leaves the text " brien' " before the next parameter starts and this causes the issue.



    I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included.

    set o = getobjects("winMgmts:!\\VS1\root\MicrosoftExchangeV2:Exchange_Mailbox.LegacyDN=[B]'/O=TEST/OU=TEST/cn=Recipients/cn=co'brien'[/B],MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'")

    I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution:

    I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried:


    Does anybody have some experience with this?


    Re: Vba To Automate Formula Application

    sorry, didn't see that.

    try this out:


    Re: Getopenfilename Runtime Error 13

    Try that - I shortend it quite a bit by the code that either got neve executed or was useless.

    Re: Getopenfilename Runtime Error 13

    one more thing in that case - don't use
    If FName = "False" Then
    If FName = False Then

    False is a VBA constant and not a string.

    As far as I could see it works fine - what do you expect it to do?

    Since you mentioned that you copied the code - perhaps it's doing something else than what you expect. To get it working on my PC I had to rem out a few lines which are using functions that I don't have.


    Re: Tab Between Controls

    Quote from Dave Hawley

    Yes, but can you see them without clicking in the said cells?

    No, the SpecialEffect is set to flat in the prperties of the controls.

    Re: Getopenfilename Runtime Error 13

    Hi Joe,

    I thought I take the time and comment all your ideas + add the solution:


    If I change the Dim FName as a String for instance it then errors in the middle bold area "If IsArray(FName) Then" as a Compile Error - Expected Array.

    Since you want the user to be able to select multiple files, you will receive an array - To Dim FNam as a String wouldn't work.


    I tried deleting the Option Explicit but that didn't do anything.

    Option Explicit is a directive for the VBA intepretar. All what it says is that any used variable has to be explicitly dimensioned. If you leave this option away, you just won't have to dimension variables. Excel could do it automatically for you.

    You can solve the issue by replacing the row If FName = "False" Then
    with If Not IsArray(FName) Then

    Re Caption, If you define a variable in the Userform, you can change that variable instead of the Caption.


    Re: Vba To Automate Formula Application


    The easiest solution would be to delete the "Formula sheet" and to enter in E2 the formula =(B2+C2)*(D2+1)/B2
    You can than fill the column with this formula...