• 0

Anyone know how to "program" for Excel?


Question

18 answers to this question

Recommended Posts

  • 0

You can write scripts within Excel using Visual Basic for Applications (VBA). You have to go into Options -> Customize Ribbon and ensure the Developer tab is enabled to get access to those tools.

What are you looking to accomplish?

  • 0
  On 02/07/2024 at 22:52, Dick Montage said:

I have worked with VSTOs within the office suite. But I think you’ll be best served by telling people what you want to achieve and dropping the vagueness.

Expand  

I wasn't being vague. I was doing the initial post as fast as I could because the bus was coming, then I got a little busy. Now I have time to give a description of what I need.

  • 0

A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion.

What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows.

We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: 

(Fixed Dollar Amount)

Column A...........Column B

Row 1

2

3

4

5

6

7

8

9

10 and so on.

(Earned Amount)

I don't know if I explained it well.

 

  • 0
  On 03/07/2024 at 01:34, branfont said:

A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion.

What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows.

We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: 

(Fixed Dollar Amount)

Column A...........Column B

Row 1

2

3

4

5

6

7

8

9

10 and so on.

(Earned Amount)

I don't know if I explained it well.

 

Expand  

Sure, I understand your requirement. You want a VBA script that will help you track sales in two columns, with a fixed monetary amount at the top, and the total earned amount at the bottom of each column. Here's a VBA script that accomplishes this:

1. It initializes a fixed amount at the top of columns A and B.
2. As you enter sales amounts in columns A and B, it updates the total earned amount at the bottom of each column.

```vba
Sub TrackSales()
    Dim ws As Worksheet
    Dim fixedAmount As Double
    Dim lastRowA As Long, lastRowB As Long
    Dim totalA As Double, totalB As Double

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name

    ' Set the fixed amount
    fixedAmount = 1000 ' Change this to your desired fixed amount

    ' Place the fixed amount at the top of columns A and B
    ws.Range("A1").Valueount
    ws.Range("B1").Valueount

    ' Find the last row in columns A and B
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Calculate the total earned amount in columns A and B
    totalA = Application.WorksheetFunction.Sum(ws.Range("A2:AastRowA))
    totalB = Application.WorksheetFunction.Sum(ws.Range("B2:BastRowB))

    ' Place the total earned amount at the bottom of columns A and B
    ws.Range("AastRowA + 1).Value = totalA
    ws.Range("BastRowB + 1).Value = totalB
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A2:BMMe.Rows.Count)) Is Nothing Then
        Call TrackSales
    End If
End Sub
```

### Explanation:

1. **TrackSales Subroutine**:
   - This subroutine sets a fixed amount at the top of columns A and B (you can change the fixed amount as needed).
   - It finds the last used row in each column.
   - It calculates the total earned amount in each column (excluding the fixed amount at the top).
   - It places the total earned amount at the bottom of each column.

2. **Worksheet_Change Event**:
   - This event triggers whenever a change is made in columns A or B.
   - It calls the `TrackSales` subroutine to update the total earned amount.

### Usage:

1. Open your Excel workbook and press `Alt + F11` to open the VBA editor.
2. Insert a new module and copy the `TrackSales` subroutine into it.
3. Go to the sheet where you want to track sales, right-click the sheet tab, select `View Code`, and paste the `Worksheet_Change` event into the sheet module.
4. Adjust the sheet name and fixed amount as needed in the `TrackSales` subroutine.
5. Save and close the VBA editor.

Now, whenever you add or change values in columns A or B, the total earned amount will be updated automatically at the bottom of each column.

  • Like 2
  • 0
  On 03/07/2024 at 01:34, branfont said:

A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion.

What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows.

We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: 

(Fixed Dollar Amount)

Column A...........Column B

Row 1

2

3

4

5

6

7

8

9

10 and so on.

(Earned Amount)

I don't know if I explained it well.

 

Expand  

You don't need to program anything - this is exactly what Excel is built for.  Just start entering the data... It's a simple math formula - should take all of 5 minutes to get this going at most. If you want to get fancy, put in pivot tables/charts. Also, if you need to actually write a script or code, just ask an AI assistant like ChatGPT / CoPilot / Gemini.

  • Like 3
  • 0
  On 03/07/2024 at 10:30, trogenda said:

Sure, I understand your requirement. You want a VBA script that will help you track sales in two columns, with a fixed monetary amount at the top, and the total earned amount at the bottom of each column. Here's a VBA script that accomplishes this:

1. It initializes a fixed amount at the top of columns A and B.
2. As you enter sales amounts in columns A and B, it updates the total earned amount at the bottom of each column.

```vba
Sub TrackSales()
    Dim ws As Worksheet
    Dim fixedAmount As Double
    Dim lastRowA As Long, lastRowB As Long
    Dim totalA As Double, totalB As Double

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name

    ' Set the fixed amount
    fixedAmount = 1000 ' Change this to your desired fixed amount

    ' Place the fixed amount at the top of columns A and B
    ws.Range("A1").Valueount
    ws.Range("B1").Valueount

    ' Find the last row in columns A and B
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Calculate the total earned amount in columns A and B
    totalA = Application.WorksheetFunction.Sum(ws.Range("A2:AastRowA))
    totalB = Application.WorksheetFunction.Sum(ws.Range("B2:BastRowB))

    ' Place the total earned amount at the bottom of columns A and B
    ws.Range("AastRowA + 1).Value = totalA
    ws.Range("BastRowB + 1).Value = totalB
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A2:BMMe.Rows.Count)) Is Nothing Then
        Call TrackSales
    End If
End Sub
```

### Explanation:

1. **TrackSales Subroutine**:
   - This subroutine sets a fixed amount at the top of columns A and B (you can change the fixed amount as needed).
   - It finds the last used row in each column.
   - It calculates the total earned amount in each column (excluding the fixed amount at the top).
   - It places the total earned amount at the bottom of each column.

2. **Worksheet_Change Event**:
   - This event triggers whenever a change is made in columns A or B.
   - It calls the `TrackSales` subroutine to update the total earned amount.

### Usage:

1. Open your Excel workbook and press `Alt + F11` to open the VBA editor.
2. Insert a new module and copy the `TrackSales` subroutine into it.
3. Go to the sheet where you want to track sales, right-click the sheet tab, select `View Code`, and paste the `Worksheet_Change` event into the sheet module.
4. Adjust the sheet name and fixed amount as needed in the `TrackSales` subroutine.
5. Save and close the VBA editor.

Now, whenever you add or change values in columns A or B, the total earned amount will be updated automatically at the bottom of each column.

Expand  

...all that work only to later find out...

 

  On 03/07/2024 at 11:50, branfont said:

I forgot to mention that I'll be using Excel on my Samsung Galaxy S23 Ultra. 

Expand  

 

  • 0
  On 03/07/2024 at 12:44, branfont said:

I never break any laws, why would I start now?!

Expand  

really sus when you said it was not for anything illegal. Why would we think that in the first place? But now that you said it's not illegal, it's definitely illegal 😛

  • 0
  On 03/07/2024 at 12:57, tsupersonic said:

really sus when you said it was not for anything illegal. Why would we think that in the first place? But now that you said it's not illegal, it's definitely illegal 😛

Expand  

Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person.

  • Facepalm 3
  • 0
  On 03/07/2024 at 13:28, branfont said:

Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person.

Expand  

🤦‍♂️  who said anything about color of skin/race?! respectfully, we're just trying to help you out. 

  • Like 2
  • 0
  On 03/07/2024 at 13:28, branfont said:

Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person.

Expand  

🤦🏻

  • 0

1.  What you're trying to do is literally what spreadsheet applications are designed for and "programming" isn't necessary. 

2.  If you're running it on your phone, just use Google sheets instead of anything Microsoft. 

3. This thread literally explains so much that wasn't asked but never really gets to the point of what was asked.

a. We know you're black.

b. We know you don't know much about spreadsheets

c. We know you assume that everyone else automatically assumes lesser of you. (I mean, how the hell would we have known anything about you without you telling us?) 

d. You assume everyone out there is white and racist. 

e. You haven't asked many questions online.  Just get to the point of what you need and more than likely we'll post a solution and get on with our day feeling like we just did something good. 

f. Don't treat Neowin like Reddit and you won't get Reddit responses. 

g.  You haven't used any of the new AI systems like chatGPT, bings integration or Google integration.  Most of those would have spit out an answer to your question in under 3 seconds.  "how do I make a spreadsheet that..  blah blah blah"

 

 

  • 0
  On 05/07/2024 at 13:25, branfont said:

I tried that and it's not working.

A1: Name

=SUM(A2:A62)

B1: Name

=SUM(B2:B62)

C1: Amount

=C1-(A62+B62)

I put them in their required place, but then I get the following error:

 

Screenshot_20240705_072450_Microsoft 365 (Office).png

Expand  

Hi Branfont 🤗.

 

Where are you placing the SUM?

 

This error usually happens when a cell is referring to another cell that refers to the cell you were referring.

 

Imagine that you have cell A1 `B1`, and in B1, you have `A1`. That's a circular reference.

 

  • 0
  On 05/07/2024 at 13:59, Jose_49 said:

Hi Branfont 🤗.

 

Where are you placing the SUM?

 

This error usually happens when a cell is referring to another cell that refers to the cell you were referring.

 

Imagine that you have cell A1 `B1`, and in B1, you have `A1`. That's a circular reference.

 

Expand  

The sums are put in their respective column.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Intel v32.0.101.6881 graphics driver fixes a popular multiplayer hero shooter by Taras Buria Intel is rolling out a new graphics driver under version 32.0.101.6881. This WHQL release does not contain much. In fact, there is only a single fix for a popular multiplayer hero shooter. The new driver fixes crashes when launching Overwatch 2 (DirectX 12) on High or Ultra graphics settings on Intel Arc A-Series graphics cards. From the changelog: Overwatch 2 (DX12) may experience an application crash while launching the game with High or Ultra graphics quality settings. Known bugs in the drive include the following: Intel Arc B-Series Graphics Products: Fortnite may experience an application crash when “Performance - Lower Graphical Fidelity” is selected as Rendering Mode. Recommendation is to use default Rendering Mode – DX12. Visual corruptions may appear in certain scenarios with multiple application interactions. Call of Duty: Black Ops 6 (DX12) may exhibit flickering corruption in certain scenes during gameplay. Returnal (DX12) may experience an application crash during gameplay with Ray-Tracing settings turned on. Call of Duty: Warzone 2.0 (DX12) may exhibit corruptions on water areas in certain scenarios. SPECapc for Maya 2024 may experience intermittent application freeze during benchmark. PugetBench for Davinci Resolve Studio V19 may experience an application crash while running the benchmark. HWiNFO may incorrectly report number of Xe Cores for certain Intel Arc B-Series Graphics Products. Intel Arc A-Series Graphics Products: Returnal (DX12) may experience an application crash during gameplay with Ray-Tracing settings turned on. Marvel’s Spider-Man 2 (DX12) may experience an application crash with Ray-Tracing and XeSS enabled. PugetBench for Davinci Resolve Studio V19 may experience an application crash while running the benchmark. Intel Core Ultra Series 1 with built-in Intel Arc GPUs: Adobe Premiere Pro may fail to import video. Mitigation is to use Intel NPU Driver version 32.0.100.3717 or lower. PugetBench for Davinci Resolve Studio V19 may experience errors intermittently with benchmark preset set to Extended. Intel Core Ultra Series 2 with built-in Intel Arc GPUs: Valorant (DX11) may fail to enumerate supported resolutions in game settings. Adobe Premiere Pro may experience an intermittent application crash. Adobe Premiere Pro may fail to import video. Mitigation is to use Intel® NPU Driver version 32.0.100.3717 or lower. PugetBench for Davinci Resolve Studio V19 may experience errors intermittently with benchmark preset set to Extended. You can install Intel 32.0.101.6881 WHQL driver on PCs with 64-bit Windows 10 and Windows 11 with the following graphics products from Intel: Discrete GPUs Integrated GPUs Intel Arc A-Series (Alchemist) Intel Arc B-Series (Battlemage) Intel Iris Xe Discrete Graphics (DG1) Intel Core Ultra Series 2 (Lunar Lake and Arrow Lake) Intel Core Ultra (Meteor Lake) Intel Core 14th Gen (Raptor Lake Refresh) Intel Core 13th Gen (Raptor Lake) Intel Core 12th Gen (Alder Lake) Intel Core 11th Gen (Tiger Lake) You can download the driver from the official website here. Full release notes are available here (PDF).
    • Just look at the shiney shiney Vista clone, ignore the fact that they are a disaster in anything AI related. Roll on the class actions for all iPhone 16 owners.
    • Since Windows 8 they still try & error a new Start Menu... And never stop. I'm afraid there is no menu than old start menu from XP - and then we still had 2 choices to select...
    • FFmpeg Batch AV Converter 3.2.4 by Razvan Serea FFmpeg Batch AV Converter is a free universal audio and video encoder, that allows to use the full potential of ffmpeg command line with a few mouse clicks in a convenient GUI. Among other things, you can drag and drop, see progress information, change encoding priority, pause and resume, and set automatic shutdown. It is good for seasoned ffmpeg users as well as beginners. It provides unlimited single or multi-file batch encoding for almost any audio/video format. You can use any set of parameters and try them before starting encoding. You can manipulate and multiplex streams, batch subtitle videos (as track and hardcoded), trim, concatenate, record screen, capture M3u8 or other media URLs. You can also access useful multimedia file information. You can manually save your favourite custom ffmpeg parameters, using a fancy encoding wizard. You can use relative/absolute output paths, automatically rename output files, overwrite them etc. Key features: Video encoding: AV1 / H264 / H265 / NVENC / QuickSync / ProRes / VP9 / Any other video format supported by ffmpeg. Audio encoding: MP3 / AAC / AC3 / FLAC / WAV / Opus / Vorbis / Any other audio format supported by ffmpeg. Unlimited batch processing Multi-file encode for thousands of files Dynamic variables for ffmpeg parameters. Automatic shutdown, with option to run post-encoding executables. Set encoding priority Stream mapping and multiplex with jobs manager. Batch mux and demux. FFmpeg presets wizard Filter files using different criteria. File multimedia info and up to 12 properties columns. Trim and concatenate files Batch image thumbnail extraction Batch image to video creation. Batch audio silence detection.. and much more... FFmpeg Batch AV Converter 3.2.4 changelog: Polish translation available (a few translations were automatic). Log refreshable during encoding, now with encoding results summary. Log saved every 60 seconds kept in case of application crash/blackout. Added warning for incompatible characters [',] found in input file path, required to be renamed to avoid errors when using ffmpeg filters. Fixed: Sequential encoding abruptly ends with some ffmpeg.exe builds. Fixed: some settings being lost after upgrades, like ffmpeg.exe path. (installer version). Fixed: %fn variable not working (working directory was not set properly). Fixed: %f2 variable not working (Windows paths conversion issue). Fixed: Keep source timestamp was not applied for some features (two pass encoding, multiplex, batch subtitling). Fixed: -vf filter previously not supported in batch subtitles parameters (Burnt subtitles) . Fixed: Try preset button sometimes rendering application unresponsive. Hopefully fixed Youtube download progress sometimes wrong by a factor of 10. Other minor corrections and bugfixes. Download: FFmpeg Batch AV Converter 3.2.4 | Portable ~20.0 MB (Open Source) Links: FFmpeg Batch Home Page | Project Page @GitHub | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Download Apple macOS 26 Tahoe, iOS 26 official stock wallpapers in high quality by Aditya Tiwari Apple's latest software design can be thought of as the tech version of 'new year, new me.' macOS 26 is one among them, featuring the "Liquid Glass" as a translucent new material that reflects and refracts its surroundings. The updated macOS design is all over the place, including the Dock, sidebars, and toolbars, which have been refined to focus more on the user's content. Apple continued its annual tradition and introduced new wallpapers custom-made for macOS 26 Tahoe to go along with the new design language. These macOS Tahoe 26 wallpapers are available in light and dark theme options, complementing the transparent menu bar, which makes the display feel bigger. To download the wallpapers to your device, click on the image to open it, then right-click on the wallpaper and select "Save Image As." Apple said during the announcement that "the new design also unlocks more personalization on the Mac. App icons come to life in light or dark appearances, colorful new light and dark tints, as well as an elegant new clear look." Apple's Liquid Design-inspired default wallpapers are also available for iOS 26 in light and dark options. The company has utilised Liquid Design extensively when upgrading the wallpaper experience on iPhones. Lock Screen wallpapers on iPhone create a 3D effect when the device is moved around, giving the illusion that the objects in the image are popping out of the screen. The time displayed on the lock screen fluidly adapts to the available space in an image for a more dynamic feel. Not just the design, Apple has further bridged the gap between iPhone and Mac by adding new Continuity features to macOS 26 on these supported Mac models. This includes the new Phone app that lets you relay phone calls from your iPhone nearby. Just like widgets, macOS 26 can populate Live Activities from a nearby iPhone, enabling you to track your Uber ride, live sports scores, or incoming dinner orders. Source: Apple via 9to5Mac [1,2]
  • Recent Achievements

    • Explorer
      MusicLover2112 went up a rank
      Explorer
    • Dedicated
      MadMung0 earned a badge
      Dedicated
    • Rookie
      CHUNWEI went up a rank
      Rookie
    • Enthusiast
      the420kid went up a rank
      Enthusiast
    • Conversation Starter
      NeoToad777 earned a badge
      Conversation Starter
  • Popular Contributors

    1. 1
      +primortal
      502
    2. 2
      ATLien_0
      268
    3. 3
      +FloatingFatMan
      253
    4. 4
      +Edouard
      202
    5. 5
      snowy owl
      169
  • Tell a friend

    Love Neowin? Tell a friend!