Number | Prompt |
|---|
10 | The attached Excel workbook contains 3 columns giving the name of each person, what hobby they're into and a rating from 0 to 10 of how good they are at it. Please write a VBA macro to colour the people whose rating is 8 or higher a light pastel pink. The macro should colour the cells containing data in the row, not the entire row. The macro should work however many names are in the table of data. |
20 | I am going to ask you to write a VBA macro in Excel. Although you are an expert in VBA coding, the person using your macro is a beginner. You should therefore write your macro to make it easy to understand for someone who is relatively new to VBA programming. In particular:
- do not use class modules
- do not use FOR EACH loops over collections
- do not use object variables
- do not use SET statements
- do not use WITH clauses
Where you have a choice of chaining commands together to make your code shorter or doing the same thing using a longer series of separate simple commands, always choose the option with more commands.
Good macros do not change the current active cell, but you should ignore this constraint and select cells before referencing or formatting them. Likewise you should select worksheets rather than just referring to them.
Do not include any instructions on how to run the macro you write: just include the code itself.
###
The attached Excel workbook contains 3 columns giving the name of each person, what hobby they're into and a rating from 0 to 10 of how good they are at it. Please write a VBA macro to colour the people whose rating is 8 or higher a light pastel pink. The macro should colour the cells containing data in the row, not the entire row. The macro should work however many names are in the table of data.
|
30 | This workbook contains a worksheet listing the English football premier league teams for the 2023/24 season and the list of teams for the 2024/25 season. In between the two seasons some teams were relegated and some were promoted. Please write a VBA macro to highlight the 2023/24 relegated teams in red and he 2024/25 promoted teams in green. |
40 | The two loops do very similar things. Is it possible please to improve your code by taking the functionality of each loop into a separate subroutine, passing in appropriate arguments? |
50 | You are an expert VBA programmer. I want you to critique the macro I will give you below, highlighting any possible problems with it. You should consider:
- any bugs you find
- things which will make the macro run more slowly - things which will make it more likely that bugs will be introduced in the future - things which could be coded better
Please do NOT add error-trapping, however.
Present your answers as a table with the following column headings:
- "Possible change" - "Description"
Keep your descriptions down to a maximum of 30 words each. You should order your changes so that the most significant and important come first in the table, and the least significant last.
Finish by showing a revised version of the macro incorporating all of the changes that you would recommend.
###
Sub ColourMusicalsBadly()
'go to the list of films
Worksheets("Films").Select
'go to the top of the list of films
Range("A2").Select
'keep going down till hit blank cell
Do Until ActiveCell.Value = " "
'if this is a musical, colour it
If ActiveCell.Offset(0, 1).Value = "musical" Then
ActiveCell.EntireRow.Interior.color = vbRed
End If
Loop
End Sub |
60 | Without changing the function or approach of the following VBA program, please tidy it up. Here's what this might include:
- Remove any variables which are not being used
- Make sure any remaining variables are declared in the declarations section at the top of the macro
- Give variables meaningful names
- Give the macro a meaningful name
- Add sensible spacing and indentation
- Make any other formatting and stylistic improvements you think are worth making
Do not add error trapping.
###
(PASTE IN THE VBA CODE HERE) |
70 | Create an SQL query to list out for each director in a database the number of films they've made, and the number of actors who have appeared in each of these films. |
80 | SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS DataType, c.max_length AS MaxLength, c.precision, c.scale, c.is_nullable, OBJECTPROPERTYEX(c.object_id, 'IsPrimaryKey') AS IsPrimaryKey, OBJECTPROPERTYEX(c.object_id, 'IsForeignKey') AS IsForeignKey FROM -- query the tables in your database sys.tables AS t -- for each table, get its columns and data types INNER JOIN sys.columns AS c ON t.object_id = c.object_id INNER JOIN sys.types AS ty ON c.user_type_id = ty.user_type_id WHERE -- look at user tables only (not system tables) t.type = 'U' -- ignore database diagrams and t.name != 'sysdiagrams' ORDER BY -- list alphabetically by table and within table by column t.name, c.column_id; |
90 | Create an SQL query to list out for each director in a database the number of films they've made, and the number of actors who have appeared in each of these films. The rest of this prompt gives the schema of this database to help you.
###
SQL GIVING SCHEMA GOES HERE |
100 | Remove the TOP (1000) and square brackets from the following SQL query, put the commas after each column name (and not before), remove any database or schema prefixes, give the table name a single character lower case alias using "AS" and then prefix each column name with this alias.
###
QUERY TO BE FORMATTED GOES HERE |
110 | You are an experienced programmer, with the ability to explain things to less experienced people in concise English without filler words or unnecessary jargon. You like to indent your code so that it looks neat and to add brief comments to explain what you're doing at each stage of a process. Write a Python program , including a comment at the start of your program explaining that it was created by an AI tool. Here's what the program should do. ### """For any given website URL, list out all of its external links in a table, giving for each link the URL being linked to and the visible text. """ |
120 | Remove any references to pandas and present the information as two columns of data without using a datatable, giving the external link URL and the visible text of it. Also remove the reference to the urlparse module and test for external links the hard way. |
130 | For the Python code in this file, please remove all of the underscores from variable names and put them in camel case. instead. Please also add more comments explaining what's going on in the code. Also, the code does two similar things - please create a function to count the number of words, then call it twice to shorten the code. |
