Cornelius’ answer is partially correct. His code gets the current instance and then makes a new instance. GetObject only ever gets the first instance, no matter how many instances are available. The question I believe is how can you get a specific instance from among many instances. For a VBA project, make two modules, one … Read more
I use and recommend Rubberduck. Description from the website: Rubberduck is a very active open-source COM add-in project that integrates with the Visual Basic Editor to enable the features every programmer wants to have in their IDE. From unit testing to source control, from code inspections to refactorings, programming in VBA will never be the … Read more
You would call the sub as EnterCellValueMonthNumber “N23:Q23”, 1 No brackets. Or Call EnterCellValueMonthNumber(“N23:Q23”, 1) Brackets, and Call before it. Also, your Sub is expecting a Range object as the first argument and you’re supplying a string; you should change the signature of the sub to: Sub EnterCellValueMonthNumber(cells As String, number As Integer) Also, I’m … Read more
First the good news. This code does what you want (please note the “line numbers”) Sub a() 10: On Error GoTo ErrorHandler 20: DivisionByZero = 1 / 0 30: Exit Sub ErrorHandler: 41: If Err.Number <> 0 Then 42: Msg = “Error # ” & Str(Err.Number) & ” was generated by ” _ & Err.Source … Read more
Let’s say that your time value is in cell A1 then in A2 you can put: =A1*1000*60*60*24 or simply: =A1*86400000 What I am doing is taking the decimal value of the time and multiply it by 1000 (milliseconds) and 60 (seconds) and 60 (minutes) and 24 (hours). You will then need to format cell A2 … Read more
You’re currently creating some of your cells twice, which is why it’s all going wrong Firstly, I’d suggest you move the cell style creation to nearer the top of your code. Remember – cell styles are scoped to a workbook, so don’t create one per cell! CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); … Read more
You could take a look at: HSSFDateUtil.isCellDateFormatted() See the POI Horrible Spreadsheet Format API for more details on HSSFDateUtil: http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDateUtil.html That also provides some helper methods for returning Excel getExcelDate() and Java dates getJavaDate(). You need to be somewhat wary of different date formats though…
Write is a special statement designed to generate machine-readable files that are later consumed with Input. Use Print to avoid any fiddling with data. Print #2, “Start postion = “; StarRange Print #2, “End position = “; EndRange
I had forgotten to add a reference to “Microsoft ActiveX Data Objects 2.5 Library”: This reference is required for early binding. How to get to that reference: Tools > References > Check the checkbox in front of “Microsoft ActiveX Data Objects 2.5 Library” Other libraries that work include: Microsoft ActiveX Data Objects 2.6 Library Microsoft … Read more
Add the following to the top of your module: Option Private Module From MSDN: When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at module level, are still available within the project containing the module, but they are not available to other applications or projects.