Why is some of the colorization of cells not occurring, and is there a connection between that and the “Cannot perform runtime binding” err msg?

I’m getting the following err msg: This is occurring in this code: private bool GetContractForDescription(string desc) { int DESCRIPTION_COL_NUM = 2; int CONTRACT_COL_NUM = 7; bool contractVal = false; int rowsUsed = _xlBook.Worksheets[“PivotData”].UsedRange.Rows.Count; int colsUsed = _xlBook.Worksheets[“PivotData”].UsedRange.Columns.Count; string colsUsedAsAlpha ReportRunnerConstsAndUtils.GetExcelColumnName(colsUsed); string endRange = string.Format(“{0}{1}”, colsUsedAsAlpha, rowsUsed); Range sourceData = _xlBook.Worksheets[“PivotData”].Range[string.Format(“A2:{0}”, endRange)]; // This is blowing […]

Qlik Sense: Adding function to my Apps so renew data on excel can be imported?

I would like to ask for some advise as below: My Qlik license (QAP) is not allow to use HUB to edit APPs, it is for display APPs to our portal which is working fine. We had separated Qlik HUB server to created APPs. The problem is we want our display APPs on portal also […]

VBA – Graceful ways of calling function that doesn't return anything

VBA: Can someone list graceful ways of calling a function that doesn’t return values? public function myDoSomeTasksFunc() myForm.Banner.Caption = “Make sure to select settings before you begin..” NumTimesFuncIsCalled = NumTimesFuncIsCalled + 1 myTotalU = 0 maxRows = 5 mpForm.pgMain.Enabled = False myForm.cbProcess.Enabled = False End Function When I call the function dim myErr = myDoSomeTasksFunc() […]

How to protect a sheet using VBA, whilst allowing the group/ungroup functionality to work?

I am using the following code to protect all the worksheets in a workbook: Sub protect_all_sheets() top: pass = InputBox(“Please input password to protect all sheets:”) repass = InputBox(“Please verify password”) If Not (pass = repass) Then MsgBox “Error: Passwords do not match” GoTo top End If optimize_vba True For i = 1 To Worksheets.Count […]

Complex shading between multiple lines on graph

All, I would like to create different colour shading between two lines and the shading colour dependant on whether the point is above “Baseline” or not. Current Graph below; For instance I have shaded in (on paint) how I would like the end result to look for either case; If anyone can let me know […]

Deselecting Pivot Table Items Error

I’m trying to use the code in this link, but I keep getting the runtime error 438 “Object doesn’t support this property or method” for the line ActiveSheet.PivotItems(1).Visible = True Does anyone know what’s going on? Full Code: Sub DeleteAllFields() Dim i As Long ActiveSheet.PivotItems(1).Visible = True For i = 2 To ActiveSheet.PivotItems.Count ActiveSheet.PivotItems(i).Visible = […]

uniqueness of active sheet VBA EXCEL

I have a question, I am having in one basic sheet, rows, for each row I create new sheet, each new sheet has in cell A3 value that I would like to use as a name for my new active sheet. Here the problem starts, as in excel the name of the sheet can only […]

How to prevent automatic change of OleObjectBlob in Excel workbook?

I’m using git to store and Excel sheet containing macro’s, it has to be backwards compatible for Excel 2003, therefore it is stored as an .xls file. Each time I load the sheet and save it, it is changed automagically. When I use Beyond Compare to diff the changes, the OleObjectBlob property is changed. For […]

Filter excel data by most recent date

I have a list of IDs, dates and scores and I need to get the score corresponding to the latest date. The data looks like this: ID | Date | Score ___________________________ 123 | 03/15/2016 | 10 123 | 08/10/2016 | 4 456 | 09/01/2016 | 7 456 | 02/02/2016 | 10 678 | 07/01/2016 […]

Upload via SFTP using Excel VBA code

I have tried to use the following code, which I found in a previous SO question, to upload a generated .php file to my shared server via FTP. I am using Excel 2016 on Windows 10, and trying to upload to a server with 1and1. Public Sub FtpSend() Dim vPath As String Dim vFile As […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.