Posts by James125

    School boy error, solved now;


    Code
    Sub lookup_macro()
    'sht2fill.name = o271120b
    
    Dim sht2fill As Worksheet: Set sht2fill = ActiveSheet
    Dim refbk As Workbook
    P253_Open (sht2fill.Name) 'opens o271120b.xlsb
    Set refbk = Workbooks(sht2fill.Name & ".xlsb")
    
    End Sub

    I'm making a summary workbook. Each worksheet name represents a workbook. I want to then set the workbook I have just opened as 'refbk', though I am having trouble.


    Code
    Sub lookup_macro()
    'sht2fill.name = o271120b
    
    Dim sht2fill As Worksheet: Set sht2fill = ActiveSheet
    Dim refbk As Workbook
    P253_Open (sht2fill.Name) 'opens o271120b.xlsb
    Set refbk = (sht2fill.Name) & ".xlsb"
    
    End Sub

    I often have a problem with the vbaProject.bin file corrupting and not allowing me to open that excel workbook. Here is my solution that has worked a few times;

    • Rename the file extension to .xls
    • On the developer tab disable all macros
    • With excel closed Hold Ctrl, double-click to open the bad workbook in safemode
    • Hopefully it opens
    • View the VBA code and remove and export it all
    • Save the bad file with your preferred extension
    • Reopen the file, import the VBA modules and save
    • Reopen and enable macros
    • Try to back things up properly in future

    Hello


    I have had a lot of corrupted vbaProject.bin files. I tends to happen to one particular work book. Is there something in the code that causes it to corrupt itself?


    No Corruption vbaProject.bin = 81KB
    With Corruption vbaProject.bin = 57KB


    I have a workbook with a chart-sheet on it. It has two series, one from workbook A and one from workbook B. If I delete the series from workbook B, the chart book should update it's links to show that only workbook A is linked. However if I update it still shows both books as linked. I tried running;


    Code
    Sub chart_book_sub
    ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
    End Sub


    but all I get is runtime error 1004
    Method 'UpdateLink' of object'_Workbook' failed


    I have also tried to go Data, Connections tab, Edit links, Update links. But the link to workbook B remains.


    It seems the only way I can update the links is to save and re-open the workbook. Does anyone know how to update the links without having to do this?

    Currently I have one massive module with 800 lines of code & I want to seperate it into seperate manageable chunks. First step is to get data from the inputs sheet. I'm trying to make the variables public so they can be used in other modules.


    [VBA]
    Option Explicit
    Public FD_Ratio As Double
    Public gears As Double
    Public TM As Double
    Public Wheel_Diameter As Double
    Public F0 As Double
    Public F1 As Double
    Public F2 As Double
    Public Kr As Double
    Public SM As Double
    Public PI As Double
    With ThisWorkbook.Sheets("Inputs")
    gears = .Cells(9, Columns.Count).End(xlToLeft).Column - 10
    End With


    Public Gear_ratios(gears) As Double[/VBA]


    The problem here is the length of some variables ie Gear_ratios is defined by variables on the sheet. How can I get around this?

    I have a spreadsheet full of times formatted as 13.09.2017 10:31:35 DD.MM.YYYY HH:MM:SS
    I want to change them to a Number which should be 42991.438599537 in this example.
    This is my attempted code;


    Hello


    I have a whole workbook of charts with series references such as;


    =SERIES([o040914c.xls]csv!$GD$1,[o040914c.xls]csv!$A$2:$A$18103,[o040914c.xls]csv!$GD$2:$GD$18103,4)


    sometimes I want to be able to make changes to multiple charts and series, for example replacing the workbook reference with another one.


    Eg [o040914c.xls] to [o130814a.xls] (British date format)


    -Also the excel I am using is 2003.

    Re: How to use a constant from the WorksheetFunction.Max?


    OK royUK I have declared test_leng as long but still get the same error;


    Compile Error:
    Constant expression required.


    Code
    Sub Make_a_dyno_file() 
         
    Const test_leng As Long = WorksheetFunction.Max(ThisWorkbook.Sheets("Vehicle Data").Range("F:F"))
         '=886 in this case
        Dim Velocity(0 To test_leng) 
         
    End Sub


    I then tried using Dim instead of Const, but I got the equals highlighted and the following error;


    Compile Error:
    Expected: end of statement


    Code
    Sub Make_a_dyno_file()
    
    
    Dim test_leng As Long = WorksheetFunction.Max(ThisWorkbook.Sheets("Vehicle Data").Range("F:F"))
    '=886 in this case
        Dim Velocity(0 To test_leng) 
         
    End Sub

    Hello all. I am getting an error with this code, the test_leng is a constant of 886, yet I can't declare it as a constant either.


    Compile Error:
    Constant expression required.



    Code
    Sub Make_a_dyno_file()
    
    
    test_leng = WorksheetFunction.Max(ThisWorkbook.Sheets("Vehicle Data").Range("F:F"))
    '=886 in this case
    Dim Velocity(0 To test_leng)
    
    
    End Sub

    I want to colour my combobox items with respect to the colour of the seriescollection that they represent. However I get this error;


    Compile Error
    Invalid qualifier


    and then 'Color' highlighted;
    this.Items.Add (Color.Red)



    Re: Read the series formula into VBA.


    so far I have;


    Code
    Private Sub CommandButton1_Click()
    the_series_Formula = ActiveChart.SeriesCollection(2).Formula
    Debug.Print the_series_Formula
    the_column_letter_loc = InStr(1, the_series_Formula, "$") + 1
    Debug.Print the_column_letter_loc
    the_column_letter = Right(Left(the_series_Formula, the_column_letter_loc), 1)
    Debug.Print the_column_letter
    End Sub


    The problem is it only gives me the letter of the 1st series of data not the 2nd. Why is this?

    I have 2 series of data on a excel line graph. One of which is;
    =SERIES(Sheet1!$G$1,Sheet1!$E$2:$E$12615,Sheet1!$G$2:$G$12615,2)


    I want to be able to produce a macro that can read this series formula. So that I can then find which column the Y-axis variable come from. In this case 'G'.


    thank you

    Solved!!


    Solved it! Hopefully someone else will find this handy!


    I want to read the formula from a cell, ie =AD203-(BC2*(1-(1/BB2))), specifically the '203' so I can then modify it and then replace it with another number. The number may be 1to3 digits long and may be proceeded by letters and signs brackets etc. My first task is to see how long the number is and record it as count, however my code doesn't appear to work.



    Re: Say NO to would you like to re-open workbook prompt


    I think this works...well it did but sometime it crashes excel instead



    Thanks for your help everyone! :guitar: