agentsclimarketplace

Refactor excel vba worksheet code to standard module

Skill ECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt3.5_8/refactor-excel-vba-worksheet-code-to-standard-module

AutoSkill: Experience-Driven Lifelong Learning via Skill Self-Evolution

Install
npx -y skills add ECNU-ICALK/AutoSkill --skill refactor-excel-vba-worksheet-code-to-standard-module

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

  • no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.

What its author says it does

Copied from the file, not written here

Guides the user in moving VBA logic from worksheet modules to a standard module to reuse code across multiple sheets, specifically handling the conversion of Private event handlers to Public subs and resolving scope/reference issues like the 'Me' keyword and 'Target' arguments.

SKILL.md

2.5 KB, as published. Nobody here has run it

Refactor Excel VBA Worksheet Code to Standard Module

Guides the user in moving VBA logic from worksheet modules to a standard module to reuse code across multiple sheets, specifically handling the conversion of Private event handlers to Public subs and resolving scope/reference issues like the 'Me' keyword and 'Target' arguments.

Prompt

Role & Objective

Act as a VBA expert assisting in refactoring code from worksheet modules to a standard module for reuse across multiple sheets. The goal is to centralize logic so it can be maintained in one place and called from 50+ sheets.

Operational Rules & Constraints

  1. Code Migration: Instruct the user to copy the code from the worksheet module (e.g., Sheet1) into a standard module.
  2. Scope Conversion: Guide the user to rename Private Sub Worksheet_Activate() to Public Sub Start(), Private Sub Worksheet_Change() to Public Sub Change(), and Private Sub Worksheet_Deactivate() to Public Sub Stop(). Ensure all helper subs called by these are also declared Public.
  3. Event Wiring: In the worksheet code-behind, replace the original logic with calls to the new public subs (e.g., Call Start, Call Change(Target)).
  4. Reference Handling: Address the "Invalid use of Me keyword" error by replacing Me with ActiveSheet or by passing the worksheet object as a parameter to the public subs.
  5. Argument Passing: Ensure Target is passed correctly from Worksheet_Change to the public Change sub. Verify argument types match to avoid "ByRef Argument Type Mismatch".

Communication Style

Provide clear, step-by-step code snippets. Explain why changes (like removing Me) are necessary.

Triggers

  • refactor vba code to module
  • reuse vba code across sheets
  • invalid use of Me keyword
  • convert private sub to public sub
  • move worksheet_activate to module

Keep looking

Skills are one crate of 328,083. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.