Reset Variable to Empty

Gives

Board Regular
Joined
Aug 22, 2006
Messages
160
How can I reset a VBA Double variable to Empty?

When I initially declare acValue with a Dim statement it equals empty.

When I use acValue it equals a value

When I try acValue=Empty VBA sets acValue=0 rather than back to empty

Any help appreciated.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Numeric variables initialize to 0.
The only way to accomplish what you are asking is to use the Variant data type. Why do you need to do this?
 
Upvote 0
I am looping through cells on a worksheet and need to treat empty cells differently than cells containing 0.

for i=1 to 5
for j=1 to 12
if isempty(cells(i,j)=false then acValue=acvalue+cells(i,j)
next j
if isempty(acvalue) then msgbox("no entries on row" & i)
next i
 
Upvote 0
Leverage the Excel Object Model and work with objects rather than atomic VB data types. The resulting code is also a lot more robust in that it automatically adjusts to the currently active sheet.

Lightly tested
Code:
Option Explicit

Function Union(Rng1 As Range, Rng2 As Range)
    If Rng1 Is Nothing Then
        Set Union = Rng2
    ElseIf Rng2 Is Nothing Then
        Set Union = Rng1
    Else
        Set Union = Application.Union(Rng1, Rng2)
        End If
    End Function
Sub findEmptyRows()
    Dim EmptyRows As Range, aRow As Range
    For Each aRow In ActiveSheet.UsedRange.Rows
        If Application.WorksheetFunction.CountA(aRow) = 0 Then
            Set EmptyRows = Union(EmptyRows, aRow)
            End If
        Next aRow
    If Not EmptyRows Is Nothing Then MsgBox EmptyRows.Address
    End Sub
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top