Posts by James125

  • Opening workbook using a sheet name

    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

  • Opening workbook using a sheet name

    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

  • Recovering the vbaProject.bin

    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

  • Corrupt vbaProject.bin

    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


  • How can I update links in excel?

    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?

  • Collecting data from sheet and then passing it to other modules

    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?

  • How to change a Date and Time to a Number?

    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;


  • Modifying a chart series in VBA

    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.

  • How to use a constant from the WorksheetFunction.Max?

    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

  • How to use a constant from the WorksheetFunction.Max?

    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

  • Add colours to items in a combobox list

    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)



  • Read the series formula into VBA

    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?

  • Read the series formula into VBA

    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] Read in cell formula then modify the formula

    Solved!!


    Solved it! Hopefully someone else will find this handy!


  • [SOLVED] Read in cell formula then modify the formula

    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.



  • Say NO to would you like to re-open workbook prompt

    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: