Using EVAL on a Chart
I've got my chart reference in a string text (made from =CONCATing a few cells). Is there a way I can use =eval() on the chart reference to evaluate this string as the chart reference.
See also questions close to this topic
Database (Excel) Access Speed - Using Open XML SDK in Visual Studio C# (DOM Approach)
I mostly write number-crunching programs using Visual Studio C# (2019) where I am simply taking input data, calculating results and displaying it. No complicated Network or Internet programming. Think first or second college level programming coarse from the early 1990's.
For inputs I was reading in data from an excel file using the following directive:
using Excel = Microsoft.Office.Interop.Excel;
This proved to be very slow when executing the program. I then learned this way of accessing an Excel file is no longer supported and has been superseded by Open XML SDK. Please see the following link to the Microsoft Dev Center page:
For what I want to do the Document Object Model(DOM) approach seems most appropriate for the thousands of individual excel cells I want to read as input data. However, the Microsoft Dev Center is certainly not the most user-friendly resource and the code example provided for reading an Excel file using this DOM approach is writing to a console which I'm not using. I never did get my code to work.
Long and short of it is, I got my code working using the GetCellValue Method:
However, this 'GetCellValue' method is still taking way too long. I need to read in thousands or tens of thousands of Excel input data cells in seconds or fractions of seconds not 20 seconds to a minute.
I think if I had an example of the DOM method reading in Excel data to an Array Variable (instead of writing to the console) it would help. Can anyone provide an example of such code?
Excel VBA Google Places API (Nearby Search) not returning results
I am calling this VBA function from Excel which uses the Google Places API Nearby Search function and is not returning results. I know there are results since this HTTP call returns results for the same input parameters. Do you know why the function is not returning results?
https://maps.googleapis.com/maps/api/place/nearbysearch/json?location=45.5662453,-122.6628821& radius=1500&type=park&key= AIzaSyCbBAbRZG0yhCHjJLaKjv8ARp2J6pv1wSQ
Public Function GetNearbyPark(latitude As Double, longitude As Double, Radius As Integer) As String
'----------------------------------------------------------------------------------------------------- 'This function returns the park name for a given latitude and longitude and radius using the Google 'Places Nearby Search API. 'Radius is in meters '-----------------------------------------------------------------------------------------------------
'Declaring the necessary variables. Dim apiKey As String Dim xmlhttpRequest As Object Dim xmlDoc As Object Dim xmlStatusNode As Object Dim xmlNearbyParkNameNode As Object Dim xmlNearbyParkAddressNode As Object 'Set your API key in this variable. 'Here is the ONLY place in the code where you have to put your API key. apiKey = "AIzaSyCbBAbRZG0yhCHjJLaKjv8ARp2J6pv1wSQ" 'Check that an API key has been provided. If apiKey = vbNullString Or apiKey = "The API Key" Then GetNearbyPark = "Empty or invalid API Key" Exit Function End If 'Generic error handling. On Error GoTo errorHandler 'Create the request object and check if it was created successfully. Set xmlhttpRequest = CreateObject("MSXML2.ServerXMLHTTP") If xmlhttpRequest Is Nothing Then GetNearbyPark = "Cannot create the request object" Exit Function End If 'Create the request based on Google Places API. Parameters (from Google page): '- Longitude '- Latitude '- Radius 'xmlhttpRequest.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _ '& "&address=" & Application.EncodeURL(address) & "&key=" & apiKey, False Debug.Print "At API call" xmlhttpRequest.Open "GET", "https://maps.googleapis.com/maps/api/place/nearbysearch/xml?" & "location=" & "latitude" & "," & longitude & "&radius=" & Radius & "&type=park&key=" & apiKey, False 'Send the request to the Google server. xmlhttpRequest.send 'Create the DOM document object and check if it was created successfully. Set xmlDoc = CreateObject("MSXML2.DOMDocument") If xmlDoc Is Nothing Then GetNearbyPark = "Cannot create the DOM document object" Exit Function End If 'Read the XML results from the request. xmlDoc.LoadXML xmlhttpRequest.responseText 'Get the value from the status node. Set xmlStatusNode = xmlDoc.SelectSingleNode("//status") Debug.Print xmlStatusNode 'Based on the status node result, proceed accordingly. Select Case UCase(xmlStatusNode.Text) Case "OK" 'The API request was successful. 'At least one result was returned. 'Get the park name and address node values of the first result. Set xmlNearbyParkNameNode = xmlDoc.SelectSingleNode("//result/name") 'Set xmlNearbyParkAddressNode = xmlDoc.SelectSingleNode("//result/vicinity") Debug.Print xmlNearbyParkNameNode 'Return the park name and address as text 'GetNearbyPark = xmlNearbyParkNameNode.Text & ", " & xmlNearbyParkAddressNode.Text GetNearbyPark = xmlNearbyParkNameNode.Text Case "ZERO_RESULTS" 'The geocode was successful but returned no results. GetNearbyPark = "No park exists within the radius of the defined coordinates" Case "OVER_DAILY_LIMIT" 'Indicates any of the following: '- The API key is missing or invalid. '- Billing has not been enabled on your account. '- A self-imposed usage cap has been exceeded. '- The provided method of payment is no longer valid ' (for example, a credit card has expired). GetNearbyPark = "Billing or payment problem" Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the quota limit. GetNearbyPark = "Quota limit exceeded" Case "REQUEST_DENIED" 'The API did not complete the request. GetNearbyPark = "Server denied the request" Case "INVALID_REQUEST" 'The API request is empty or is malformed. GetNearbyPark = "Request was empty or malformed" Case "UNKNOWN_ERROR" 'The request could not be processed due to a server error. GetNearbyPark = "Unknown error" Case Else 'Just in case... GetNearbyPark = "Error" End Select 'Release the objects before exiting (or in case of error). errorHandler: Set xmlStatusNode = Nothing Set xmlNearbyParkNameNode = Nothing Set xmlNearbyParkAddressNode = Nothing Set xmlDoc = Nothing Set xmlhttpRequest = Nothing End Function
How Can I create a distribution chart in Microsoft Excel?
I want to create a histogram in Microsoft Excel. I think this should be easy but I am oddly having some difficulties.
I have the following table:
Bins | Frequency 50000 | 800 100000 | 500 150000 | 300
and so on. The Bins column shows the bin width for this histogram. The Frequency column shows the number of values in each bin. How can I then turn the above table into a histogram?
Intersection Error with Multiple VBA Loops
I'm trying to write a code to move rows into separate sheets based on input, I have it working for one case as shown below:
Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range If Intersect(Target, me.Range("AN:AN")) Is Nothing Then Exit Sub For Each C In Intersect(Target, me.Range("AN:AN")).Cells If C.Text "Y" Then C.EntireRow.Copy Worksheets("Closed").Cells(Rows.Couint, "AN").End(xlUp).Offset(1).EntireRow C.EntireRow.Delete End If Next End Sub
Now I want to have a seperate case for a seperate column reference but when I try and repeat the code into the same subroutine I get an error '1004 intersect error', I have tried quite a few things but the error seems to always be thrown with the second intersect statement:
Private Sub Worksheet_Change(ByVal Target As Range) Dim C, B As Range If Intersect(Target, me.Range("AN:AN")) Is Nothing Then Exit Sub For Each C In Intersect(Target, me.Range("AN:AN")).Cells If C.Text "Y" Then C.EntireRow.Copy Worksheets("Closed").Cells(Rows.Count, "AN").End(xlUp).Offset(1).EntireRow C.EntireRow.Delete End If Next If Intersect(Target, me.Range("D:D")) Is Nothing Then Exit Sub For Each B In Intersect(Target, me.Range("D:D")).Cells If B.Text "Q" Then B.EntireRow.Copy Worksheets("Quoted").Cells(Rows.Count, "D").End(xlUp).Offset(1).EntireRow B.EntireRow.Delete End If Next End Sub
I tried to group the two presence checks into one statement at the start but it still threw an error during the second loop, as shown:
If Intersect(Target, me.Range("AN:AN, D:D")) Is Nothing Then Exit Sub
How to replace "S" in a formula to be able to have a variable instead of this letter S?
I think the question is simple, the answer maybe not.
Sheet1.Cells().Value = "=SUM(E" & i & ":S" & i & ")/" & n
In short, I would like to replace the letter S with a variable (from E to AB, I don't stop to Z!) to be able to put this simple formula in a serie of cells. When writing this question, I was thinking of using an array. I think I will have a look in this direction. But, if you have a more simple answer, do not hesitate. Thank you.
Excel - Turning a negative number into a null
I am trying to get the change percentage between 2 numbers and that giver result must have 2 decimal places. Currently the formula I am using is
=(Value_2-Value_1)/Value_2and the result is 0.00%. Of course that by looking to the column
Value_2we know the result of that formula isn't exactly 0.00%. In fact, it's -0.0044.... So it's not
Null, but I want it to be.
I have another column called
Nulland there I have a formula that returns an X if the "Change" column value is equal to "0".
=IF(Change=0, "X", ""). Obviously this isn't returning the X, because the value ins't negative.
My question is...How do I turn this "0.00%" which in fact is "0.0044...%" to be a null?
Forecasting in excel
I'm Very new to forecasting in excel.Have a data for some products with previous 30 days and 60 days data in it and wanted to know if that can be used to forecast the data for next 30 days. I've an Idea of doing it if there is only one set of attributes by using Forecast tool in Excel but new to this kind of problem. Any suggestion would help. Thanks in advance.
excel content - remove content from the cell
Greeting Dear All,
Would be happy to get help for the same.
Here is a Name of the client in the 1st cell and name and address together in 2nd cell..
in 3rd cell i want result with just address.
In short - its like search the content of 1st cell and delete from 2nd cell.
Thanks a lot to you all.
Making charts appear on individual sheets
Charts are appearing one on top of the other on one sheet instead of on individual sheets in the workbook
My first attempt as using VBA to produce charts. I have 17 sheets in my workbook and want to produce charts on all but sheet 1 (AllData). I'm getting a basic chart for all 16 sheets (haven't managed to workout how to put the sheet name as the title yet) but they are all appearing one on top of the other on sheet 2. My aim is to have them appearing just below lastrow.offset(3) on the correct sheets. I'd welcome any advice...
Sub Macro33() Application.ScreenUpdating = True Dim sh As Worksheet Dim lastRow As Long Dim ActiveWorksheet As Long Dim ThisWorksheet As Long Dim N As Integer Dim rng As range Dim cell As range Dim r As range Dim j As Integer Dim x As Integer x = Sheets.Count For N = 2 To x lastRow = Cells(Rows.Count, 1).End(xlUp).Row range("I1:I" & lastRow).Select ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select ActiveChart.SetSourceData Source:=Sheets(N).range("I2:I" & lastRow) ActiveChart.Axes(xlCategory).Select Application.CutCopyMode = False Application.CutCopyMode = False ActiveChart.FullSeriesCollection(1).XValues = Sheets(N).range("J2:J" & lastRow) Application.ScreenUpdating = False Next N range("A1").Select Application.ScreenUpdating = True End Sub
Using Excel VBA to individually color y-axis lables
I'm trying to figure out how to change the color of individual y-axis labels programmatically for a bar chart. Here's what the chart currently looks like:
Here's how I'd like it to look. (The colors on the "RX 5700 XT" lines are in red -- this is via a hasty PhotoShop hack, so this isn't set in stone, just an example of where I'm headed).
The problem is, I can't seem to find a way to programmatically get at the individual text labels. The actual text isn't necessarily hard-coded, but based on matching some pattern I want to change the text colors -- a secondary coding of data, if you will.
Background: This is for my charts at Tom's Hardware, for GPU reviews specifically. In this case, I have all GPUs tested with one CPU colored one way (lighter grey and red), and all GPUs tested with a different CPU colored a different way (darker grey and red). However, I also want to potentially differentiate between AMD and Nvidia GPUs -- so highlight the AMD GPUs in Dark Red text as an example.
There are about 60 charts total, so manually changing colors on each chart after generation would be extremely inefficient. I could just use different bar chart colors, but that also gets messy. Theoretically, I want to get at myChart.Axes(xlCategory).Format.TextFrame2.TextRange ... but TextFrame2 is a read-only property. I recorded a macro where I changed the axis font color, but even that macro fails to play back properly. :\
How do I show data for 4 weeks on a Google Sheets chart by week day?
I want to show data for 4 weeks on a Google Sheets chart.
I have tried to do this and it is mostly ok as this is correct:
- The X-axis shows the days of the week
- The Y-axis is the weight number
Issues I have with my current implementation are:
- I had to make a separate series for each week when I wanted it automatically group the data by week number. (I then can't name each series)
- The data is off by one day. Monday's data doesn't show which means Tuesday's data is showing in place of Monday and so on.
Here is a link to the google sheet and a screenshot below: https://docs.google.com/spreadsheets/d/147RMW2JE7MoWz53EZuB-nHj-p_xeKez_ze_0mqq8NGA/edit?usp=sharing
Here the lines are shown grouped by each week AND data is shown for each day