Forum Discussion

kristi_duong's avatar
kristi_duong
Copper Contributor
Sep 10, 2023

Macro code not running

Macro code not running when it opened from a different computer. The file ran fine on its original author's computer but other users could not run it. Is there any changes of setting need to be made on original file so others can run the macro?

Thank you!

 

Sub Results()
'
' Macro1 Macro
'
Dim iCap, iOcc, iLoan, iResult As Integer
Dim InitialPurchaseCap, InitialPurchaseOcc, InitialLoanInterest As Double
Dim PurchaseCap, PurchaseOcc, LoanInterest As Double
Dim CapInc, OccInc, InterestInc As Double

'Set the increments
CapInc = 0.005
OccInc = 0.05
InterestInc = 0.0025
iResult = 0

PurchaseCap = Range("PurchaseCap").Value - CapInc
InitialPurchaseCap = PurchaseCap
PurchaseOcc = Range("PurchaseOccupancy").Value - OccInc
InitialPurchaseOcc = PurchaseOcc
LoanInterest = Range("MortgageInterest").Value - InterestInc
InitialLoanInterest = LoanInterest

While (PurchaseCap < 0.15)
PurchaseCap = PurchaseCap + CapInc
PurchaseOcc = InitialPurchaseOcc
While (PurchaseOcc < 1)
PurchaseOcc = PurchaseOcc + OccInc
LoanInterest = InitialLoanInterest
While (LoanInterest < 0.1)
LoanInterest = LoanInterest + InterestInc
Range("PurchaseCap").Select
ActiveCell.Value = PurchaseCap

Range("PurchaseOccupancy").Select
ActiveCell.Value = PurchaseOcc

Range("MortgageInterest").Select
ActiveCell.Value = LoanInterest
'Copy the calculation to a new cell

Application.CutCopyMode = False
Range("ResultsPrefInt").Select
Selection.Copy
Range("Results").Offset(iResult).Select

' ActiveCell.Offset(rowOffset:=iResult).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
iResult = iResult + 1

Wend
Wend
Wend



End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Range("N29:S29").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("AA2").Select
Application.CutCopyMode = False
Range("N29:S29").Select
Selection.Copy
Range("AA2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=1
End Sub

Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 11, 2023

    kristi_duong 

    Macros written in VBA (Visual Basic for Applications) can be opened and executed in most versions of Microsoft Excel, including Excel for Windows and Excel for Mac. However, there are some considerations to keep in mind:

    1. Compatibility Mode: When opening a workbook with macros in a newer version of Excel, it may open in "Compatibility Mode" to ensure compatibility with older Excel versions. Macros should still work in Compatibility Mode, but some newer features may not be available.
    2. Macros in Excel for Mac: Macros created in Excel for Windows can be opened and run in Excel for Mac. However, there might be some differences in behavior or features due to platform variations.
    3. Macro Security: Excel has security features that can prevent macros from running, especially if they are from an untrusted source. Users may need to adjust their macro security settings to allow macros to run.
    4. VBA Code Compatibility: While VBA is largely consistent across Excel versions, there can be minor differences. It is a good practice to test macros on different Excel versions to ensure they work as expected.
    5. ActiveX Controls: If your macros use ActiveX controls, their behavior can vary between Excel versions, especially between Excel for Windows and Excel for Mac.
    6. 64-bit vs. 32-bit: Excel is available in both 32-bit and 64-bit versions on Windows. Macros should be compatible with the version of Excel they are running on. Compatibility issues may arise if a 32-bit Excel attempts to run a 64-bit macro, or vice versa.
    7. Add-Ins: If your macros depend on specific add-ins, those add-ins must also be available and compatible with the version of Excel being used.
    8. External Data: Macros that interact with external data sources, such as databases or web services, may require appropriate drivers or connectivity settings on the computer where they are executed.

    To ensure maximum compatibility, it is a good practice to:

    • Write clear and well-documented macros.
    • Test macros on different versions of Excel.
    • Consider platform-specific behaviors (Windows vs. Mac).
    • Provide clear instructions to users on how to enable macros and any prerequisites.

    By following these best practices, you can create macros that are more likely to work across different Excel versions. To maximize compatibility, it is a good practice to test your macros on different versions of Excel and provide clear instructions to users on how to enable macros and any prerequisites. Additionally, consider using error handling in your VBA code to gracefully handle situations where certain features or libraries may not be available in a specific Excel version.

    The text and steps were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources